package templates

import (
	"context"
	"errors"
	"fmt"
	"time"

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

var (
	ErrNotFound      = errors.New("template not found")
	ErrCannotModify  = errors.New("cannot modify archived template")
	ErrAlreadyActive = errors.New("template is already active")
)

// Pool abstracts pgxpool.Pool.
type Pool interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	Begin(ctx context.Context) (pgx.Tx, error)
}

// Repository handles database persistence for report templates.
type Repository struct {
	pool Pool
}

// NewRepository constructs a Repository.
func NewRepository(pool Pool) *Repository {
	return &Repository{pool: pool}
}

// List returns report templates optionally filtered by code and status.
func (r *Repository) List(ctx context.Context, reportCode, status string) ([]ReportTemplate, error) {
	query := `SELECT id, report_code, version, name, coalesce(description, ''),
		template_data, sample_data, status, is_default, created_by, updated_by,
		created_at, updated_at
		FROM report_templates
		WHERE 1=1`
	args := []any{}
	idx := 1

	if reportCode != "" {
		query += fmt.Sprintf(` AND report_code = $%d`, idx)
		args = append(args, reportCode)
		idx++
	}
	if status != "" {
		query += fmt.Sprintf(` AND status = $%d`, idx)
		args = append(args, status)
		idx++
	}
	query += ` ORDER BY report_code ASC, version DESC`

	rows, err := r.pool.Query(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	items := make([]ReportTemplate, 0)
	for rows.Next() {
		var t ReportTemplate
		if err := rows.Scan(
			&t.ID, &t.ReportCode, &t.Version, &t.Name, &t.Description,
			&t.TemplateData, &t.SampleData, &t.Status, &t.IsDefault,
			&t.CreatedBy, &t.UpdatedBy, &t.CreatedAt, &t.UpdatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, t)
	}
	return items, rows.Err()
}

// GetByID returns a template by ID.
func (r *Repository) GetByID(ctx context.Context, id int64) (*ReportTemplate, error) {
	query := `SELECT id, report_code, version, name, coalesce(description, ''),
		template_data, sample_data, status, is_default, created_by, updated_by,
		created_at, updated_at
		FROM report_templates
		WHERE id = $1`

	var t ReportTemplate
	err := r.pool.QueryRow(ctx, query, id).Scan(
		&t.ID, &t.ReportCode, &t.Version, &t.Name, &t.Description,
		&t.TemplateData, &t.SampleData, &t.Status, &t.IsDefault,
		&t.CreatedBy, &t.UpdatedBy, &t.CreatedAt, &t.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return &t, nil
}

// GetActiveByCode returns the currently active template for a given report code.
func (r *Repository) GetActiveByCode(ctx context.Context, reportCode string) (*ReportTemplate, error) {
	query := `SELECT id, report_code, version, name, coalesce(description, ''),
		template_data, sample_data, status, is_default, created_by, updated_by,
		created_at, updated_at
		FROM report_templates
		WHERE report_code = $1 AND status = 'active'
		ORDER BY version DESC LIMIT 1`

	var t ReportTemplate
	err := r.pool.QueryRow(ctx, query, reportCode).Scan(
		&t.ID, &t.ReportCode, &t.Version, &t.Name, &t.Description,
		&t.TemplateData, &t.SampleData, &t.Status, &t.IsDefault,
		&t.CreatedBy, &t.UpdatedBy, &t.CreatedAt, &t.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return &t, nil
}

// NextVersion returns the next version number for a report code.
func (r *Repository) NextVersion(ctx context.Context, reportCode string) (int, error) {
	var maxVer int
	err := r.pool.QueryRow(ctx, `SELECT coalesce(max(version), 0) FROM report_templates WHERE report_code = $1`, reportCode).Scan(&maxVer)
	if err != nil {
		return 1, err
	}
	return maxVer + 1, nil
}

// Create inserts a new report template row.
func (r *Repository) Create(ctx context.Context, t *ReportTemplate) error {
	ver, err := r.NextVersion(ctx, t.ReportCode)
	if err != nil {
		return err
	}
	t.Version = ver
	if t.Status == "" {
		t.Status = "draft"
	}

	query := `INSERT INTO report_templates (
		report_code, version, name, description, template_data, sample_data,
		status, is_default, created_by, updated_by, created_at, updated_at
	) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, now(), now())
	RETURNING id, created_at, updated_at`

	return r.pool.QueryRow(ctx, query,
		t.ReportCode, t.Version, t.Name, t.Description, t.TemplateData, t.SampleData,
		t.Status, t.IsDefault, t.CreatedBy, t.UpdatedBy,
	).Scan(&t.ID, &t.CreatedAt, &t.UpdatedAt)
}

// Update updates an existing template (only if draft or active).
func (r *Repository) Update(ctx context.Context, id int64, in *UpdateTemplateInput, userID *int64) error {
	existing, err := r.GetByID(ctx, id)
	if err != nil {
		return err
	}
	if existing.Status == "archived" {
		return ErrCannotModify
	}

	name := existing.Name
	if in.Name != nil && *in.Name != "" {
		name = *in.Name
	}
	desc := existing.Description
	if in.Description != nil {
		desc = *in.Description
	}
	tplData := existing.TemplateData
	if in.TemplateData != nil {
		tplData = *in.TemplateData
	}
	sampleData := existing.SampleData
	if in.SampleData != nil {
		sampleData = *in.SampleData
	}
	status := existing.Status
	if in.Status != nil && *in.Status != "" {
		status = *in.Status
	}

	query := `UPDATE report_templates SET
		name = $1, description = $2, template_data = $3, sample_data = $4,
		status = $5, updated_by = $6, updated_at = now()
		WHERE id = $7`

	_, err = r.pool.Exec(ctx, query, name, desc, tplData, sampleData, status, userID, id)
	return err
}

// Activate sets this version to active, and archives/demotes any previously active version for the same report_code.
func (r *Repository) Activate(ctx context.Context, id int64, userID *int64) error {
	tpl, err := r.GetByID(ctx, id)
	if err != nil {
		return err
	}
	if tpl.Status == "active" {
		return nil // already active
	}

	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	// Demote current active templates for this report_code to archived
	_, err = tx.Exec(ctx, `UPDATE report_templates SET status = 'archived', is_default = false, updated_at = now(), updated_by = $1 WHERE report_code = $2 AND status = 'active'`, userID, tpl.ReportCode)
	if err != nil {
		return err
	}

	// Activate selected template
	_, err = tx.Exec(ctx, `UPDATE report_templates SET status = 'active', is_default = true, updated_at = now(), updated_by = $1 WHERE id = $2`, userID, id)
	if err != nil {
		return err
	}

	return tx.Commit(ctx)
}

// Duplicate creates a new draft version from an existing template.
func (r *Repository) Duplicate(ctx context.Context, id int64, userID *int64) (*ReportTemplate, error) {
	orig, err := r.GetByID(ctx, id)
	if err != nil {
		return nil, err
	}

	nextVer, err := r.NextVersion(ctx, orig.ReportCode)
	if err != nil {
		return nil, err
	}

	copyName := fmt.Sprintf("%s (v%d Draft)", orig.Name, nextVer)
	newTpl := &ReportTemplate{
		ReportCode:   orig.ReportCode,
		Version:      nextVer,
		Name:         copyName,
		Description:  orig.Description,
		TemplateData: orig.TemplateData,
		SampleData:   orig.SampleData,
		Status:       "draft",
		IsDefault:    false,
		CreatedBy:    userID,
		UpdatedBy:    userID,
		CreatedAt:    time.Now(),
		UpdatedAt:    time.Now(),
	}

	err = r.Create(ctx, newTpl)
	if err != nil {
		return nil, err
	}
	return newTpl, nil
}