package main

import (
	"context"
	"crypto/sha256"
	_ "embed"
	"fmt"
	"strings"

	"github.com/jackc/pgx/v5"
)

//go:embed schema.sql
var schema string

func schemaHash() string { return fmt.Sprintf("%x", sha256.Sum256([]byte(schema))) }

// Serving never changes schema. Existing untracked tables require review.
func migrate(ctx context.Context, conn *pgx.Conn) error {
	tx, err := conn.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)
	if _, err = tx.Exec(ctx, "SELECT pg_advisory_xact_lock(486290431)"); err != nil {
		return err
	}
	var tracked bool
	if err = tx.QueryRow(ctx, "SELECT to_regclass('public.content_schema_migrations') IS NOT NULL").Scan(&tracked); err != nil {
		return err
	}
	if tracked {
		var hash string
		if err = tx.QueryRow(ctx, "SELECT sha256 FROM content_schema_migrations WHERE version=1 AND (SELECT count(*) FROM content_schema_migrations)=1").Scan(&hash); err != nil {
			return err
		}
		if hash != schemaHash() {
			return fmt.Errorf("schema checksum mismatch; refusing migration")
		}
		return tx.Commit(ctx)
	}
	var exists bool
	if err = tx.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name IN ('blog_user','blog_tag','blog_post','blog_post_tags','blog_comment'))`).Scan(&exists); err != nil {
		return err
	}
	if exists {
		return fmt.Errorf("untracked blog tables already exist; refusing automatic adoption")
	}
	sql := strings.Replace(schema, "BEGIN;", "", 1)
	sql = strings.TrimSuffix(strings.TrimSpace(sql), "COMMIT;")
	if _, err = tx.Exec(ctx, sql); err != nil {
		return err
	}
	if _, err = tx.Exec(ctx, `CREATE TABLE content_schema_migrations(version integer PRIMARY KEY, sha256 text NOT NULL, applied_at timestamptz NOT NULL DEFAULT now())`); err != nil {
		return err
	}
	if _, err = tx.Exec(ctx, "INSERT INTO content_schema_migrations(version,sha256) VALUES(1,$1)", schemaHash()); err != nil {
		return err
	}
	return tx.Commit(ctx)
}