// Package service — schema migration support.
// Mirrors: odoo/modules/migration.py (safe subset)
package service

import (
	"context"
	"fmt"
	"log"

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

	"odoo-go/pkg/orm"
)

// MigrateSchema compares registered model fields with existing database columns
// and adds any missing columns. This is a safe, additive-only migration:
// it does NOT remove columns, change types, or drop tables.
//
// Mirrors: odoo/modules/loading.py _auto_init() — the part that adds new
// columns when a model gains a field after the initial CREATE TABLE.
func MigrateSchema(ctx context.Context, pool *pgxpool.Pool) error {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return fmt.Errorf("migrate: begin: %w", err)
	}
	defer tx.Rollback(ctx)

	added := 0
	for _, m := range orm.Registry.Models() {
		if m.IsAbstract() {
			continue
		}

		// Check if the table exists at all
		var tableExists bool
		err := tx.QueryRow(ctx,
			`SELECT EXISTS (
				SELECT 1 FROM information_schema.tables
				WHERE table_name = $1 AND table_schema = 'public'
			)`, m.Table()).Scan(&tableExists)
		if err != nil || !tableExists {
			continue // Table doesn't exist yet; InitDatabase will create it
		}

		// Get existing columns for this table
		existing := make(map[string]bool)
		rows, err := tx.Query(ctx,
			`SELECT column_name FROM information_schema.columns
			 WHERE table_name = $1 AND table_schema = 'public'`,
			m.Table())
		if err != nil {
			continue
		}
		for rows.Next() {
			var col string
			rows.Scan(&col)
			existing[col] = true
		}
		rows.Close()

		// Add missing columns
		for _, f := range m.StoredFields() {
			if f.Name == "id" {
				continue
			}
			if existing[f.Column()] {
				continue
			}

			sqlType := f.SQLType()
			if sqlType == "" {
				continue
			}

			alter := fmt.Sprintf(`ALTER TABLE %q ADD COLUMN %q %s`,
				m.Table(), f.Column(), sqlType)
			if _, err := tx.Exec(ctx, alter); err != nil {
				log.Printf("migrate: warning: add column %s.%s: %v", m.Table(), f.Column(), err)
			} else {
				log.Printf("migrate: added column %s.%s (%s)", m.Table(), f.Column(), sqlType)
				added++
			}
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("migrate: commit: %w", err)
	}
	if added > 0 {
		log.Printf("migrate: %d column(s) added", added)
	} else {
		log.Println("migrate: schema up to date")
	}
	return nil
}