package models

import (
	"database/sql"
)

type Product struct {
	ID          int
	Slug        string
	Name        string
	Description string
	Price       int
	Image_URL   string
	Active      bool
}

type Variant struct {
	ID                  int
	Product_ID          int
	Size                string
	Printful_Variant_ID sql.NullString
	Stock               int
	Sort_Order          int
}

type Product_With_Variant struct {
	Product_ID          int
	Variant_ID          int
	Product_Name        string
	Size                string
	Price               int
	Image_URL           string
	Printful_Variant_ID sql.NullString
}

type Product_Image struct {
	ID         int
	Product_ID int
	Image_URL  string
	Sort_Order int
}

func Get_All_Products(db *sql.DB) ([]Product, error) {
	rows, err := db.Query(`
		SELECT id, slug, name, description, price, image_url
		FROM products
		WHERE active = true
		ORDER BY created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var products []Product
	for rows.Next() {
		var p Product
		if err := rows.Scan(&p.ID, &p.Slug, &p.Name, &p.Description, &p.Price, &p.Image_URL); err != nil {
			return nil, err
		}
		products = append(products, p)
	}
	return products, rows.Err()
}

func Get_Product_By_Slug(db *sql.DB, slug string) (*Product, error) {
	var p Product
	err := db.QueryRow(`
		SELECT id, slug, name, description, price, image_url, active
		FROM products
		WHERE slug = $1 AND active = true
	`, slug).Scan(&p.ID, &p.Slug, &p.Name, &p.Description, &p.Price, &p.Image_URL, &p.Active)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

func Get_Product_By_ID(db *sql.DB, id int) (*Product, error) {
	var p Product
	err := db.QueryRow(`
		SELECT id, slug, name, description, price, image_url, active
		FROM products
		WHERE id = $1
	`, id).Scan(&p.ID, &p.Slug, &p.Name, &p.Description, &p.Price, &p.Image_URL, &p.Active)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

func Get_Product_Variants(db *sql.DB, product_id int) ([]Variant, error) {
	rows, err := db.Query(`
		SELECT id, size, printful_variant_id, stock
		FROM variants
		WHERE product_id = $1
		ORDER BY sort_order
	`, product_id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var variants []Variant
	for rows.Next() {
		var v Variant
		v.Product_ID = product_id
		if err := rows.Scan(&v.ID, &v.Size, &v.Printful_Variant_ID, &v.Stock); err != nil {
			return nil, err
		}
		variants = append(variants, v)
	}
	return variants, rows.Err()
}

func Get_Variant_By_ID(db *sql.DB, variant_id int) (*Product_With_Variant, error) {
	var pwv Product_With_Variant
	err := db.QueryRow(`
		SELECT v.id, v.product_id, v.size, v.printful_variant_id, p.name, p.price, p.image_url
		FROM variants v
		JOIN products p ON p.id = v.product_id
		WHERE v.id = $1
	`, variant_id).Scan(
		&pwv.Variant_ID,
		&pwv.Product_ID,
		&pwv.Size,
		&pwv.Printful_Variant_ID,
		&pwv.Product_Name,
		&pwv.Price,
		&pwv.Image_URL,
	)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &pwv, nil
}

func Get_Product_Images(db *sql.DB, product_id int) ([]Product_Image, error) {
	rows, err := db.Query(`
		SELECT id, product_id, image_url, sort_order
		FROM product_images
		WHERE product_id = $1
		ORDER BY sort_order
	`, product_id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var images []Product_Image
	for rows.Next() {
		var img Product_Image
		if err := rows.Scan(&img.ID, &img.Product_ID, &img.Image_URL, &img.Sort_Order); err != nil {
			return nil, err
		}
		images = append(images, img)
	}
	return images, rows.Err()
}