sabisan/internal/store/migrations.go

package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"

	"golang.org/x/crypto/bcrypt"
)

func (s *Store) Migrate(adminUsername, adminPassword string) error {
	stmts := []string{
		`create table if not exists site_content (
			id integer primary key check (id = 1),
			hero_title text not null,
			hero_subtitle text not null,
			positioning text not null default '',
			hero_cta_label text not null default '',
			hero_cta_url text not null default '',
			secondary_cta_label text not null default '',
			secondary_cta_url text not null default '',
			intro_title text not null,
			intro_text text not null,
			service_one_title text not null default '',
			service_one_text text not null default '',
			service_two_title text not null default '',
			service_two_text text not null default '',
			service_three_title text not null default '',
			service_three_text text not null default '',
			process_one_title text not null default '',
			process_one_text text not null default '',
			process_two_title text not null default '',
			process_two_text text not null default '',
			process_three_title text not null default '',
			process_three_text text not null default '',
			about_name text not null,
			about_role text not null,
			about_bio text not null,
			studio_philosophy text not null default '',
			studio_approach text not null default '',
			studio_credentials text not null default '',
			service_area text not null default '',
			email text not null,
			phone text not null,
			location text not null,
			hero_image text not null,
			about_image text not null
		)`,
		`create table if not exists projects (
			id integer primary key autoincrement,
			slug text not null unique,
			title text not null,
			location text not null,
			year text not null,
			category text not null,
			summary text not null default '',
			scope text not null default '',
			status text not null default '',
			position integer not null default 0,
			description text not null,
			cover_image text not null,
			featured integer not null default 0,
			created_at datetime not null default current_timestamp
		)`,
		`create table if not exists project_images (
			id integer primary key autoincrement,
			project_id integer not null references projects(id) on delete cascade,
			path text not null,
			caption text not null,
			position integer not null default 0
		)`,
		`create table if not exists services (
			id integer primary key autoincrement,
			title text not null,
			summary text not null,
			details text not null,
			position integer not null default 0,
			active integer not null default 1,
			created_at datetime not null default current_timestamp
		)`,
		`create table if not exists faqs (
			id integer primary key autoincrement,
			question text not null,
			answer text not null,
			position integer not null default 0,
			active integer not null default 1,
			created_at datetime not null default current_timestamp
		)`,
		`create table if not exists contact_requests (
			id integer primary key autoincrement,
			name text not null,
			email text not null,
			message text not null,
			created_at datetime not null default current_timestamp
		)`,
		`create table if not exists admin_users (
			id integer primary key autoincrement,
			username text not null unique,
			password_hash blob not null
		)`,
		`create table if not exists sessions (
			token_hash text primary key,
			user_id integer not null references admin_users(id) on delete cascade,
			expires_at datetime not null
		)`,
	}
	for _, stmt := range stmts {
		if _, err := s.db.Exec(stmt); err != nil {
			return err
		}
	}
	siteColumns := map[string]string{
		"positioning":         "text not null default 'Residential architecture and interiors in London'",
		"hero_cta_label":      "text not null default 'Start an enquiry'",
		"hero_cta_url":        "text not null default '/contact'",
		"secondary_cta_label": "text not null default 'View projects'",
		"secondary_cta_url":   "text not null default '/projects'",
		"service_one_title":   "text not null default 'Residential architecture'",
		"service_one_text":    "text not null default 'Carefully planned homes, extensions, and spatial changes shaped around daily life.'",
		"service_two_title":   "text not null default 'Interior architecture'",
		"service_two_text":    "text not null default 'Layouts, materials, storage, lighting, and built-in elements considered as one whole.'",
		"service_three_title": "text not null default 'Early consultation'",
		"service_three_text":  "text not null default 'Focused advice for feasibility, priorities, budgets, and the next practical steps.'",
		"process_one_title":   "text not null default 'Listen'",
		"process_one_text":    "text not null default 'Clarify the site, constraints, ambitions, and what the project needs to solve.'",
		"process_two_title":   "text not null default 'Shape'",
		"process_two_text":    "text not null default 'Develop a spatial direction through sketches, references, plans, and material thinking.'",
		"process_three_title": "text not null default 'Refine'",
		"process_three_text":  "text not null default 'Coordinate details, decisions, and documentation so the work can move forward clearly.'",
		"studio_philosophy":   "text not null default 'The studio favours calm, durable spaces where proportion, daylight, materials, and storage do practical work without visual noise.'",
		"studio_approach":     "text not null default 'Projects begin with listening and careful briefing, then move through measured options, clear priorities, and detailed decisions at a pace suited to the client and site.'",
		"studio_credentials":  "text not null default 'Independent architecture and interior design practice working across residential projects, renovations, and compact cultural spaces.'",
		"service_area":        "text not null default 'London and selected UK projects'",
	}
	for column, definition := range siteColumns {
		if err := s.ensureColumn("site_content", column, definition); err != nil {
			return err
		}
	}
	projectColumns := map[string]string{
		"summary":  "text not null default ''",
		"scope":    "text not null default ''",
		"status":   "text not null default 'Completed'",
		"position": "integer not null default 0",
	}
	for column, definition := range projectColumns {
		if err := s.ensureColumn("projects", column, definition); err != nil {
			return err
		}
	}
	if err := s.ensureColumn("contact_requests", "phone", "text not null default ''"); err != nil {
		return err
	}
	if err := s.ensureColumn("contact_requests", "project_type", "text not null default ''"); err != nil {
		return err
	}
	if err := s.ensureColumn("contact_requests", "project_location", "text not null default ''"); err != nil {
		return err
	}
	if err := s.ensureColumn("contact_requests", "budget_range", "text not null default ''"); err != nil {
		return err
	}
	if err := s.ensureColumn("contact_requests", "timeline", "text not null default ''"); err != nil {
		return err
	}
	if err := s.ensureColumn("contact_requests", "status", "text not null default 'new'"); err != nil {
		return err
	}
	if err := s.ensureColumn("contact_requests", "notes", "text not null default ''"); err != nil {
		return err
	}
	return s.seed(adminUsername, adminPassword)
}

func (s *Store) ensureColumn(table, column, definition string) error {
	rows, err := s.db.Query(fmt.Sprintf("pragma table_info(%s)", table))
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var cid int
		var name, typ string
		var notNull int
		var defaultValue sql.NullString
		var pk int
		if err := rows.Scan(&cid, &name, &typ, &notNull, &defaultValue, &pk); err != nil {
			return err
		}
		if name == column {
			return nil
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	_, err = s.db.Exec(fmt.Sprintf("alter table %s add column %s %s", table, column, definition))
	return err
}

func (s *Store) seed(adminUsername, adminPassword string) error {
	var count int
	if err := s.db.QueryRow(`select count(*) from site_content`).Scan(&count); err != nil {
		return err
	}
	if count == 0 {
		_, err := s.db.Exec(`insert into site_content (
			id, hero_title, hero_subtitle, positioning, hero_cta_label, hero_cta_url, secondary_cta_label, secondary_cta_url,
			intro_title, intro_text,
			service_one_title, service_one_text, service_two_title, service_two_text, service_three_title, service_three_text,
			process_one_title, process_one_text, process_two_title, process_two_text, process_three_title, process_three_text,
			about_name, about_role, about_bio, studio_philosophy, studio_approach, studio_credentials, service_area,
			email, phone, location, hero_image, about_image
		) values (1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
			"Archi Folio",
			"Spatial design, architecture, and interiors shaped through quiet detail.",
			"Residential architecture and interiors in London",
			"Start an enquiry",
			"/contact",
			"View projects",
			"/projects",
			"Selected residential and cultural spaces",
			"A compact portfolio for showing image-led architectural work, interior concepts, and project narratives.",
			"Residential architecture",
			"Carefully planned homes, extensions, and spatial changes shaped around daily life.",
			"Interior architecture",
			"Layouts, materials, storage, lighting, and built-in elements considered as one whole.",
			"Early consultation",
			"Focused advice for feasibility, priorities, budgets, and the next practical steps.",
			"Listen",
			"Clarify the site, constraints, ambitions, and what the project needs to solve.",
			"Shape",
			"Develop a spatial direction through sketches, references, plans, and material thinking.",
			"Refine",
			"Coordinate details, decisions, and documentation so the work can move forward clearly.",
			"Alex Morgan",
			"Architect & Interior Designer",
			"I design calm, functional spaces with attention to proportion, material, light, and the daily rituals of the people who use them.",
			"The studio favours calm, durable spaces where proportion, daylight, materials, and storage do practical work without visual noise.",
			"Projects begin with listening and careful briefing, then move through measured options, clear priorities, and detailed decisions at a pace suited to the client and site.",
			"Independent architecture and interior design practice working across residential projects, renovations, and compact cultural spaces.",
			"London and selected UK projects",
			"studio@example.com",
			"+44 20 0000 0000",
			"London, United Kingdom",
			"/static/placeholders/hero.svg",
			"/static/placeholders/about.svg",
		)
		if err != nil {
			return err
		}
	}

	if err := s.db.QueryRow(`select count(*) from projects`).Scan(&count); err != nil {
		return err
	}
	if count == 0 {
		projects := []Project{
			{Slug: "courtyard-house", Title: "Courtyard House", Location: "Bath, UK", Year: "2025", Category: "Residential", Summary: "A private house arranged around a quiet internal garden.", Scope: "Architecture, interiors, material strategy", Status: "Completed", Position: 1, Description: "A private house organized around a quiet internal garden, using warm timber, stone, and filtered daylight.", CoverImage: "/static/placeholders/project-1.svg", Featured: true},
			{Slug: "atelier-apartment", Title: "Atelier Apartment", Location: "London, UK", Year: "2024", Category: "Interior", Summary: "A compact apartment refit with integrated storage and a flexible work area.", Scope: "Interior architecture, joinery, lighting", Status: "Completed", Position: 2, Description: "A compact apartment refit with integrated storage, gallery-like surfaces, and a flexible work area.", CoverImage: "/static/placeholders/project-2.svg", Featured: true},
			{Slug: "gallery-room", Title: "Gallery Room", Location: "Amsterdam, NL", Year: "2024", Category: "Cultural", Summary: "A small exhibition environment for sculpture, events, and shifting light.", Scope: "Spatial design, exhibition planning", Status: "Concept", Position: 3, Description: "A small exhibition environment designed for shifting light levels, sculpture, and intimate events.", CoverImage: "/static/placeholders/project-3.svg", Featured: true},
		}
		for _, p := range projects {
			id, err := s.CreateProject(context.Background(), p)
			if err != nil {
				return err
			}
			for i := 0; i < 3; i++ {
				if _, err := s.db.Exec(`insert into project_images (project_id, path, caption, position) values (?, ?, ?, ?)`, id, p.CoverImage, p.Title, i); err != nil {
					return err
				}
			}
		}
	}

	if err := s.db.QueryRow(`select count(*) from services`).Scan(&count); err != nil {
		return err
	}
	if count == 0 {
		services := []Service{
			{Title: "Residential architecture", Summary: "New homes, extensions, and spatial reconfiguration for private clients.", Details: "Suitable for homeowners who need a clear architectural direction, measured priorities, planning support, and coordinated design decisions from early brief through detailed development.", Position: 1, Active: true},
			{Title: "Renovation and extensions", Summary: "Careful upgrades to existing homes where light, storage, and flow need to work harder.", Details: "Useful for period properties, compact urban homes, and phased refurbishments where the existing building needs to be understood before design moves are made.", Position: 2, Active: true},
			{Title: "Interior architecture", Summary: "Layouts, joinery, materials, lighting, and finishes developed as one spatial system.", Details: "For clients who need the interior to feel resolved rather than decorated, with attention to proportion, thresholds, storage, and daily use.", Position: 3, Active: true},
			{Title: "Early-stage consultation", Summary: "Focused advice before committing to a larger scope of work.", Details: "A practical starting point for feasibility, budget alignment, project priorities, or deciding whether a property or idea has the right potential.", Position: 4, Active: true},
		}
		for _, service := range services {
			if err := s.CreateService(context.Background(), service); err != nil {
				return err
			}
		}
	}

	if err := s.db.QueryRow(`select count(*) from faqs`).Scan(&count); err != nil {
		return err
	}
	if count == 0 {
		faqs := []FAQ{
			{Question: "What size projects are a good fit?", Answer: "The studio is best suited to residential projects, renovations, compact interiors, and early-stage design work where careful spatial thinking is valued.", Position: 1, Active: true},
			{Question: "Where does the studio work?", Answer: "The studio is based in London and considers selected projects across the UK depending on scope, timing, and site needs.", Position: 2, Active: true},
			{Question: "Can I book a consultation before a full project?", Answer: "Yes. Early consultation is useful for clarifying feasibility, budget, priorities, and whether a larger design process is appropriate.", Position: 3, Active: true},
		}
		for _, faq := range faqs {
			if err := s.CreateFAQ(context.Background(), faq); err != nil {
				return err
			}
		}
	}

	hash, err := bcrypt.GenerateFromPassword([]byte(adminPassword), bcrypt.DefaultCost)
	if err != nil {
		return err
	}
	var user AdminUser
	err = s.db.QueryRow(`select id, username, password_hash from admin_users order by id asc limit 1`).Scan(&user.ID, &user.Username, &user.PasswordHash)
	if errors.Is(err, sql.ErrNoRows) {
		_, err = s.db.Exec(`insert into admin_users (username, password_hash) values (?, ?)`, adminUsername, hash)
		return err
	}
	if err != nil {
		return err
	}
	if user.Username == adminUsername && bcrypt.CompareHashAndPassword(user.PasswordHash, []byte(adminPassword)) == nil {
		return nil
	}
	_, err = s.db.Exec(`update admin_users set username = ?, password_hash = ? where id = ?`, adminUsername, hash, user.ID)
	if err != nil {
		return err
	}
	_, err = s.db.Exec(`delete from sessions`)
	return err
}