package postgres

import (
	"database/sql"

	"gardomatic.kleiax.de/internal/storage"
)

// SpeciesTaskTemplateModel stores recurring species task rules in PostgreSQL.
// SpeciesTaskTemplateModel implements storage.SpeciesTaskTemplateModelInterface
// and resolves global species through the requesting garden boundary.
type SpeciesTaskTemplateModel struct{ DB *sql.DB }

const speciesTaskTemplateColumns = `t.id, t.species_id, t.origin, t.title, t.description, t.trigger_type,
    t.month_from, t.day_from, t.month_to, t.day_to, t.offset_days_from, t.offset_days_to,
    t.interval_days, t.trigger_offset, t.trigger_offset_unit, t.duration, t.duration_unit,
    t.recurrence, t.recurrence_interval, t.priority, t.active, t.created_at, t.updated_at, t.version`

func scanSpeciesTaskTemplate(s scanner) (storage.SpeciesTaskTemplate, error) {
	var template storage.SpeciesTaskTemplate
	err := s.Scan(
		&template.ID, &template.SpeciesID, &template.Origin, &template.Title, &template.Description,
		&template.TriggerType, &template.MonthFrom, &template.DayFrom, &template.MonthTo,
		&template.DayTo, &template.OffsetDaysFrom, &template.OffsetDaysTo,
		&template.IntervalDays, &template.TriggerOffset, &template.TriggerOffsetUnit, &template.Duration, &template.DurationUnit,
		&template.Recurrence, &template.RecurrenceInterval, &template.Priority, &template.Active, &template.CreatedAt,
		&template.UpdatedAt, &template.Version,
	)
	return template, err
}

// Insert creates a task template for a species.
func (m SpeciesTaskTemplateModel) Insert(template storage.SpeciesTaskTemplate) (storage.SpeciesTaskTemplate, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	err := m.DB.QueryRowContext(ctx, `
		INSERT INTO species_task_templates (
			species_id, origin, title, description, trigger_type, month_from, day_from,
            month_to, day_to, offset_days_from, offset_days_to, interval_days,
            trigger_offset, trigger_offset_unit, duration, duration_unit, recurrence, recurrence_interval, priority, active)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20)
        RETURNING id, created_at, updated_at, version`,
		template.SpeciesID, template.Origin, template.Title, template.Description, template.TriggerType,
		template.MonthFrom, template.DayFrom, template.MonthTo, template.DayTo,
		template.OffsetDaysFrom, template.OffsetDaysTo, template.IntervalDays,
		template.TriggerOffset, template.TriggerOffsetUnit, template.Duration, template.DurationUnit,
		template.Recurrence, template.RecurrenceInterval, template.Priority, template.Active,
	).Scan(&template.ID, &template.CreatedAt, &template.UpdatedAt, &template.Version)
	if err != nil {
		return storage.SpeciesTaskTemplate{}, err
	}
	return template, nil
}

// Get returns a task template visible in a garden.
func (m SpeciesTaskTemplateModel) Get(gardenID, id int) (storage.SpeciesTaskTemplate, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	template, err := scanSpeciesTaskTemplate(m.DB.QueryRowContext(ctx,
		`SELECT `+speciesTaskTemplateColumns+` FROM species_task_templates t JOIN species s ON s.id = t.species_id WHERE (s.garden_id IS NULL OR s.garden_id = $1) AND t.id = $2`, gardenID, id))
	if err != nil {
		return storage.SpeciesTaskTemplate{}, recordError(err)
	}
	return template, nil
}

// GetAllForSpecies lists task templates for a species visible in a garden.
func (m SpeciesTaskTemplateModel) GetAllForSpecies(gardenID, speciesID int) ([]storage.SpeciesTaskTemplate, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	rows, err := m.DB.QueryContext(ctx, `SELECT `+speciesTaskTemplateColumns+`
        FROM species_task_templates t JOIN species s ON s.id = t.species_id
        WHERE (s.garden_id IS NULL OR s.garden_id = $1) AND t.species_id = $2
        ORDER BY t.active DESC, t.priority DESC, t.title, t.id`, gardenID, speciesID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	templates := []storage.SpeciesTaskTemplate{}
	for rows.Next() {
		template, err := scanSpeciesTaskTemplate(rows)
		if err != nil {
			return nil, err
		}
		templates = append(templates, template)
	}
	return templates, rows.Err()
}

// Update changes a task template using optimistic locking.
func (m SpeciesTaskTemplateModel) Update(gardenID int, template storage.SpeciesTaskTemplate) (storage.SpeciesTaskTemplate, error) {
	ctx, cancel := contextWithTimeout()
	defer cancel()
	err := m.DB.QueryRowContext(ctx, `
		UPDATE species_task_templates SET species_id = $1, origin = $2, title = $3, description = $4,
			trigger_type = $5, month_from = $6, day_from = $7, month_to = $8,
			day_to = $9, offset_days_from = $10, offset_days_to = $11,
			interval_days = $12, trigger_offset = $13, trigger_offset_unit = $14,
			duration = $15, duration_unit = $16, recurrence = $17, recurrence_interval = $18,
			priority = $19, active = $20,
			updated_at = CURRENT_TIMESTAMP, version = version + 1
		WHERE id = $21 AND version = $22
		  AND EXISTS (SELECT 1 FROM species s WHERE s.id = species_task_templates.species_id AND s.garden_id IS NOT DISTINCT FROM NULLIF($23, 0))
        RETURNING updated_at, version`,
		template.SpeciesID, template.Origin, template.Title, template.Description, template.TriggerType,
		template.MonthFrom, template.DayFrom, template.MonthTo, template.DayTo,
		template.OffsetDaysFrom, template.OffsetDaysTo, template.IntervalDays,
		template.TriggerOffset, template.TriggerOffsetUnit, template.Duration, template.DurationUnit,
		template.Recurrence, template.RecurrenceInterval, template.Priority, template.Active, template.ID, template.Version, gardenID,
	).Scan(&template.UpdatedAt, &template.Version)
	if err != nil {
		if err == sql.ErrNoRows {
			return storage.SpeciesTaskTemplate{}, storage.ErrEditConflict
		}
		return storage.SpeciesTaskTemplate{}, err
	}
	return template, nil
}

// Delete removes a task template visible in a garden.
func (m SpeciesTaskTemplateModel) Delete(gardenID, id int) error {
	return deleteByID(m.DB, `DELETE FROM species_task_templates t USING species s WHERE t.id = $1 AND s.id = t.species_id AND s.garden_id IS NOT DISTINCT FROM NULLIF($2, 0)`, id, gardenID)
}