package db

import (
	"context"
	"database/sql"
	"time"
)

type PanelConfig struct {
	ID               int64
	Name             string
	EmbedTitle       string
	EmbedDescription sql.NullString
	EmbedColor       string
	EmbedImage       string
	EmbedThumbnail   string
	GuildID          string
	ChannelID        sql.NullString
	MessageID        sql.NullString
	SortOrder        int
	CreatedAt        time.Time
	UpdatedAt        time.Time
	Types            []*PanelType
}

type PanelType struct {
	ID               int64
	PanelID          int64
	Name             string
	ButtonLabel      string
	ButtonColor      string
	ButtonEmoji      string
	EmbedColor       string
	EmbedTitle       string
	EmbedText        string
	ThumbnailURL     string
	ImageURL         string
	StaffRoleID      string
	CategoryID       string
	ClaimChannelID   string
	LogChannelID     string
	MaxPerUser       int
	ClaimMode        int
	ClaimReupMinutes int
	CloseRule        string
	ModalEnabled     bool
	SortOrder        int
	CreatedAt        time.Time
	UpdatedAt        time.Time
}

type PanelConfigRepo struct{ db *sql.DB }

func NewPanelConfigRepo(db *sql.DB) *PanelConfigRepo { return &PanelConfigRepo{db: db} }

const panelCols = `id,name,embed_title,embed_description,embed_color,embed_image,embed_thumbnail,guild_id,channel_id,message_id,sort_order,created_at,updated_at`

const typeCols = `id,panel_id,name,button_label,button_color,button_emoji,embed_color,embed_title,embed_text,thumbnail_url,image_url,` +
	`staff_role_id,category_id,claim_channel_id,log_channel_id,max_per_user,claim_mode,claim_reup_minutes,close_rule,modal_enabled,sort_order,created_at,updated_at`

func (r *PanelConfigRepo) List(ctx context.Context) ([]*PanelConfig, error) {
	rows, err := r.db.QueryContext(ctx, `SELECT `+panelCols+` FROM panel_configs ORDER BY sort_order,name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*PanelConfig
	for rows.Next() {
		p, err := scanPanelConfig(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	// Load types for each panel in one extra pass
	for _, p := range out {
		p.Types, _ = r.ListTypes(ctx, p.ID)
	}
	return out, nil
}

func (r *PanelConfigRepo) GetByID(ctx context.Context, id int64) (*PanelConfig, error) {
	row := r.db.QueryRowContext(ctx, `SELECT `+panelCols+` FROM panel_configs WHERE id=?`, id)
	var p PanelConfig
	err := row.Scan(&p.ID, &p.Name, &p.EmbedTitle, &p.EmbedDescription, &p.EmbedColor,
		&p.EmbedImage, &p.EmbedThumbnail, &p.GuildID, &p.ChannelID, &p.MessageID, &p.SortOrder, &p.CreatedAt, &p.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	types, err := r.ListTypes(ctx, id)
	if err != nil {
		return nil, err
	}
	p.Types = types
	return &p, nil
}

func (r *PanelConfigRepo) Create(ctx context.Context, p *PanelConfig) error {
	now := time.Now().UTC()
	res, err := r.db.ExecContext(ctx,
		`INSERT INTO panel_configs(name,embed_title,embed_description,embed_color,embed_image,embed_thumbnail,guild_id,channel_id,sort_order,created_at,updated_at)
		 VALUES(?,?,?,?,?,?,?,?,?,?,?)`,
		p.Name, p.EmbedTitle, p.EmbedDescription, p.EmbedColor, p.EmbedImage, p.EmbedThumbnail,
		p.GuildID, p.ChannelID, p.SortOrder, now, now)
	if err != nil {
		return err
	}
	p.ID, _ = res.LastInsertId()
	p.CreatedAt = now
	p.UpdatedAt = now
	return nil
}

func (r *PanelConfigRepo) Update(ctx context.Context, p *PanelConfig) error {
	now := time.Now().UTC()
	_, err := r.db.ExecContext(ctx,
		`UPDATE panel_configs SET name=?,embed_title=?,embed_description=?,embed_color=?,embed_image=?,embed_thumbnail=?,
		 guild_id=?,channel_id=?,sort_order=?,updated_at=? WHERE id=?`,
		p.Name, p.EmbedTitle, p.EmbedDescription, p.EmbedColor, p.EmbedImage, p.EmbedThumbnail,
		p.GuildID, p.ChannelID, p.SortOrder, now, p.ID)
	if err == nil {
		p.UpdatedAt = now
	}
	return err
}

func (r *PanelConfigRepo) Delete(ctx context.Context, id int64) error {
	_, err := r.db.ExecContext(ctx, `DELETE FROM panel_configs WHERE id=?`, id)
	return err
}

// ReplaceTypes deletes all types for a panel and inserts the provided list.
func (r *PanelConfigRepo) ReplaceTypes(ctx context.Context, panelID int64, types []*PanelType) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint
	if _, err := tx.ExecContext(ctx, `DELETE FROM panel_types WHERE panel_id=?`, panelID); err != nil {
		return err
	}
	now := time.Now().UTC()
	for i, t := range types {
		t.PanelID = panelID
		t.SortOrder = i
		res, err := tx.ExecContext(ctx,
			`INSERT INTO panel_types(panel_id,name,button_label,button_color,button_emoji,embed_color,embed_title,embed_text,thumbnail_url,image_url,
			 staff_role_id,category_id,claim_channel_id,log_channel_id,max_per_user,claim_mode,claim_reup_minutes,close_rule,modal_enabled,sort_order,created_at,updated_at)
			 VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
			t.PanelID, t.Name, t.ButtonLabel, t.ButtonColor, t.ButtonEmoji, t.EmbedColor, t.EmbedTitle, t.EmbedText, t.ThumbnailURL, t.ImageURL,
			t.StaffRoleID, t.CategoryID, t.ClaimChannelID, t.LogChannelID, t.MaxPerUser, t.ClaimMode, t.ClaimReupMinutes,
			t.CloseRule, boolInt(t.ModalEnabled), t.SortOrder, now, now)
		if err != nil {
			return err
		}
		t.ID, _ = res.LastInsertId()
	}
	return tx.Commit()
}

// ReorderTypes updates sort_order for the given type IDs in order.
func (r *PanelConfigRepo) ReorderTypes(ctx context.Context, panelID int64, typeIDs []int64) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint
	for i, id := range typeIDs {
		if _, err := tx.ExecContext(ctx,
			`UPDATE panel_types SET sort_order=? WHERE id=? AND panel_id=?`, i, id, panelID); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (r *PanelConfigRepo) SetDiscordMessage(ctx context.Context, id int64, channelID, messageID string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE panel_configs SET channel_id=?,message_id=?,updated_at=? WHERE id=?`,
		channelID, messageID, time.Now().UTC(), id)
	return err
}

func (r *PanelConfigRepo) GetByName(ctx context.Context, name string) (*PanelConfig, error) {
	row := r.db.QueryRowContext(ctx, `SELECT `+panelCols+` FROM panel_configs WHERE name=?`, name)
	var p PanelConfig
	err := row.Scan(&p.ID, &p.Name, &p.EmbedTitle, &p.EmbedDescription, &p.EmbedColor,
		&p.EmbedImage, &p.EmbedThumbnail, &p.GuildID, &p.ChannelID, &p.MessageID, &p.SortOrder, &p.CreatedAt, &p.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	p.Types, _ = r.ListTypes(ctx, p.ID)
	return &p, nil
}

func (r *PanelConfigRepo) IsEmpty(ctx context.Context) (bool, error) {
	var count int
	err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM panel_configs`).Scan(&count)
	return count == 0, err
}

// --- Types ---

func (r *PanelConfigRepo) ListTypes(ctx context.Context, panelID int64) ([]*PanelType, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+typeCols+` FROM panel_types WHERE panel_id=? ORDER BY sort_order,name`, panelID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*PanelType
	for rows.Next() {
		t, err := scanPanelType(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

func (r *PanelConfigRepo) CreateType(ctx context.Context, t *PanelType) error {
	now := time.Now().UTC()
	res, err := r.db.ExecContext(ctx,
		`INSERT INTO panel_types(panel_id,name,button_label,button_color,button_emoji,embed_color,embed_title,embed_text,thumbnail_url,image_url,
		 staff_role_id,category_id,claim_channel_id,log_channel_id,max_per_user,claim_mode,claim_reup_minutes,close_rule,modal_enabled,sort_order,created_at,updated_at)
		 VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
		t.PanelID, t.Name, t.ButtonLabel, t.ButtonColor, t.ButtonEmoji, t.EmbedColor, t.EmbedTitle, t.EmbedText, t.ThumbnailURL, t.ImageURL,
		t.StaffRoleID, t.CategoryID, t.ClaimChannelID, t.LogChannelID, t.MaxPerUser, t.ClaimMode, t.ClaimReupMinutes,
		t.CloseRule, boolInt(t.ModalEnabled), t.SortOrder, now, now)
	if err != nil {
		return err
	}
	t.ID, _ = res.LastInsertId()
	t.CreatedAt = now
	t.UpdatedAt = now
	return nil
}

func (r *PanelConfigRepo) UpdateType(ctx context.Context, t *PanelType) error {
	now := time.Now().UTC()
	_, err := r.db.ExecContext(ctx,
		`UPDATE panel_types SET name=?,button_label=?,button_color=?,button_emoji=?,embed_color=?,embed_title=?,embed_text=?,thumbnail_url=?,image_url=?,
		 staff_role_id=?,category_id=?,claim_channel_id=?,log_channel_id=?,max_per_user=?,claim_mode=?,claim_reup_minutes=?,close_rule=?,modal_enabled=?,sort_order=?,updated_at=?
		 WHERE id=?`,
		t.Name, t.ButtonLabel, t.ButtonColor, t.ButtonEmoji, t.EmbedColor, t.EmbedTitle, t.EmbedText, t.ThumbnailURL, t.ImageURL,
		t.StaffRoleID, t.CategoryID, t.ClaimChannelID, t.LogChannelID, t.MaxPerUser, t.ClaimMode, t.ClaimReupMinutes,
		t.CloseRule, boolInt(t.ModalEnabled), t.SortOrder, now, t.ID)
	return err
}

func (r *PanelConfigRepo) DeleteType(ctx context.Context, id int64) error {
	_, err := r.db.ExecContext(ctx, `DELETE FROM panel_types WHERE id=?`, id)
	return err
}

func scanPanelConfig(rows *sql.Rows) (*PanelConfig, error) {
	var p PanelConfig
	err := rows.Scan(&p.ID, &p.Name, &p.EmbedTitle, &p.EmbedDescription, &p.EmbedColor,
		&p.EmbedImage, &p.EmbedThumbnail, &p.GuildID, &p.ChannelID, &p.MessageID, &p.SortOrder, &p.CreatedAt, &p.UpdatedAt)
	return &p, err
}

func scanPanelType(rows *sql.Rows) (*PanelType, error) {
	var t PanelType
	var modalEnabled int
	err := rows.Scan(&t.ID, &t.PanelID, &t.Name, &t.ButtonLabel, &t.ButtonColor, &t.ButtonEmoji,
		&t.EmbedColor, &t.EmbedTitle, &t.EmbedText, &t.ThumbnailURL, &t.ImageURL,
		&t.StaffRoleID, &t.CategoryID, &t.ClaimChannelID, &t.LogChannelID,
		&t.MaxPerUser, &t.ClaimMode, &t.ClaimReupMinutes, &t.CloseRule, &modalEnabled, &t.SortOrder, &t.CreatedAt, &t.UpdatedAt)
	t.ModalEnabled = modalEnabled != 0
	return &t, err
}