package models

import (
	"database/sql"
	"time"
)

type Project struct {
	ID           int      `json:"id"`
	Title        string   `json:"title"`
	Description  string   `json:"description"`
	Status       string   `json:"status"`
	URL          string   `json:"url"`
	SourceURL    string   `json:"source_url"`
	Tags         []string `json:"tags"`
	DisplayOrder int      `json:"display_order"`
	Featured     bool     `json:"featured"`
	CreatedAt    string   `json:"created_at"`
	UpdatedAt    string   `json:"updated_at"`
}

type ProjectStore struct {
	DB *sql.DB
}

func (s *ProjectStore) ListAll() ([]Project, error) {
	rows, err := s.DB.Query(`SELECT id, title, description, status, url, source_url, tags, display_order, featured, created_at, updated_at FROM projects ORDER BY display_order ASC, created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanProjects(rows)
}

func (s *ProjectStore) ListFeatured() ([]Project, error) {
	rows, err := s.DB.Query(`SELECT id, title, description, status, url, source_url, tags, display_order, featured, created_at, updated_at FROM projects WHERE featured = 1 ORDER BY display_order ASC, created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanProjects(rows)
}

func (s *ProjectStore) GetByID(id int) (*Project, error) {
	var p Project
	err := s.DB.QueryRow(`SELECT id, title, description, status, url, source_url, tags, display_order, featured, created_at, updated_at FROM projects WHERE id = ?`, id).
		Scan(&p.ID, &p.Title, &p.Description, &p.Status, &p.URL, &p.SourceURL, &scannableJSON{&p.Tags}, &p.DisplayOrder, &p.Featured, &p.CreatedAt, &p.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

func (s *ProjectStore) Create(p *Project) error {
	p.CreatedAt = time.Now().UTC().Format(time.RFC3339)
	p.UpdatedAt = p.CreatedAt
	res, err := s.DB.Exec(`INSERT INTO projects (title, description, status, url, source_url, tags, display_order, featured, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		p.Title, p.Description, p.Status, p.URL, p.SourceURL, tagsToJSON(p.Tags), p.DisplayOrder, boolToInt(p.Featured), p.CreatedAt, p.UpdatedAt)
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	p.ID = int(id)
	return nil
}

func (s *ProjectStore) Update(p *Project) error {
	p.UpdatedAt = time.Now().UTC().Format(time.RFC3339)
	_, err := s.DB.Exec(`UPDATE projects SET title=?, description=?, status=?, url=?, source_url=?, tags=?, display_order=?, featured=?, updated_at=? WHERE id=?`,
		p.Title, p.Description, p.Status, p.URL, p.SourceURL, tagsToJSON(p.Tags), p.DisplayOrder, boolToInt(p.Featured), p.UpdatedAt, p.ID)
	return err
}

func (s *ProjectStore) Delete(id int) error {
	_, err := s.DB.Exec(`DELETE FROM projects WHERE id = ?`, id)
	return err
}

func (s *ProjectStore) ReplaceAll(projects []Project) error {
	tx, err := s.DB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.Exec(`DELETE FROM projects`); err != nil {
		return err
	}

	stmt, err := tx.Prepare(`INSERT INTO projects (title, description, status, url, source_url, tags, display_order, featured, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	for _, p := range projects {
		if p.CreatedAt == "" {
			p.CreatedAt = p.UpdatedAt
		}
		if p.UpdatedAt == "" {
			p.UpdatedAt = p.CreatedAt
		}
		_, err := stmt.Exec(p.Title, p.Description, p.Status, p.URL, p.SourceURL, tagsToJSON(p.Tags), p.DisplayOrder, boolToInt(p.Featured), p.CreatedAt, p.UpdatedAt)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

func scanProjects(rows *sql.Rows) ([]Project, error) {
	var projects []Project
	for rows.Next() {
		var p Project
		if err := rows.Scan(&p.ID, &p.Title, &p.Description, &p.Status, &p.URL, &p.SourceURL, &scannableJSON{&p.Tags}, &p.DisplayOrder, &p.Featured, &p.CreatedAt, &p.UpdatedAt); err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}