package models

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

type Order struct {
	ID                     int
	Stripe_Session_ID      string
	Stripe_Payment_Intent  string
	Email                  string
	Total                  int
	Status                 string
	Shipping_Name          string
	Shipping_Address       string
	Printful_Order_ID      string
	Created_At             time.Time
	Updated_At             time.Time
}

type Order_Item struct {
	ID         int
	Order_ID   int
	Product_ID int
	Variant_ID int
	Quantity   int
	Price      int
}

type Order_Item_Detail struct {
	Order_Item
	Product_Name string
	Size         string
}

func Create_Order(
	db *sql.DB,
	stripe_session_id string,
	stripe_payment_intent string,
	email string,
	total int,
	status string,
	shipping_name string,
	shipping_address string,
) (int, error) {
	var order_id int
	err := db.QueryRow(`
		INSERT INTO orders (
			stripe_session_id,
			stripe_payment_intent,
			email,
			total,
			status,
			shipping_name,
			shipping_address
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7)
		RETURNING id
	`, stripe_session_id, stripe_payment_intent, email, total, status, shipping_name, shipping_address).Scan(&order_id)

	return order_id, err
}

func Get_Order_By_ID(db *sql.DB, id int) (*Order, error) {
	var o Order
	err := db.QueryRow(`
		SELECT id, stripe_session_id, stripe_payment_intent, email, total, status,
		       shipping_name, shipping_address, printful_order_id, created_at, updated_at
		FROM orders
		WHERE id = $1
	`, id).Scan(
		&o.ID, &o.Stripe_Session_ID, &o.Stripe_Payment_Intent, &o.Email, &o.Total, &o.Status,
		&o.Shipping_Name, &o.Shipping_Address, &o.Printful_Order_ID, &o.Created_At, &o.Updated_At,
	)

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

func Get_Order_By_Stripe_Session(db *sql.DB, session_id string) (*Order, error) {
	var o Order
	err := db.QueryRow(`
		SELECT id, stripe_session_id, stripe_payment_intent, email, total, status,
		       shipping_name, shipping_address, printful_order_id, created_at, updated_at
		FROM orders
		WHERE stripe_session_id = $1
	`, session_id).Scan(
		&o.ID, &o.Stripe_Session_ID, &o.Stripe_Payment_Intent, &o.Email, &o.Total, &o.Status,
		&o.Shipping_Name, &o.Shipping_Address, &o.Printful_Order_ID, &o.Created_At, &o.Updated_At,
	)

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

func Update_Order(db *sql.DB, id int, fields map[string]interface{}) error {
	query := "UPDATE orders SET updated_at = NOW()"
	args := []interface{}{}
	argCount := 1

	for key, value := range fields {
		query += fmt.Sprintf(", %s = $%d", key, argCount)
		args = append(args, value)
		argCount++
	}

	query += fmt.Sprintf(" WHERE id = $%d", argCount)
	args = append(args, id)

	_, err := db.Exec(query, args...)
	return err
}

func Add_Order_Item(db *sql.DB, order_id, product_id, variant_id, quantity, price int) error {
	_, err := db.Exec(`
		INSERT INTO order_items (order_id, product_id, variant_id, quantity, price)
		VALUES ($1, $2, $3, $4, $5)
	`, order_id, product_id, variant_id, quantity, price)
	return err
}

func Get_Order_Items(db *sql.DB, order_id int) ([]Order_Item_Detail, error) {
	rows, err := db.Query(`
		SELECT oi.id, oi.order_id, oi.product_id, oi.variant_id, oi.quantity, oi.price,
		       p.name as product_name, v.size
		FROM order_items oi
		JOIN products p ON p.id = oi.product_id
		JOIN variants v ON v.id = oi.variant_id
		WHERE oi.order_id = $1
	`, order_id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []Order_Item_Detail
	for rows.Next() {
		var item Order_Item_Detail
		if err := rows.Scan(
			&item.ID, &item.Order_ID, &item.Product_ID, &item.Variant_ID,
			&item.Quantity, &item.Price, &item.Product_Name, &item.Size,
		); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}