-- Tettevi Group final order/cart/UI database patch
-- Safe to run multiple times. It creates/repairs checkout tables without deleting data.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS carts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  session_id VARCHAR(191),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_session(session_id),
  INDEX idx_user(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cart_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cart_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_cart_product(cart_id, product_id),
  INDEX idx_cart(cart_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(80) NULL UNIQUE,
  user_id INT UNSIGNED NULL,
  status VARCHAR(30) DEFAULT 'pending',
  subtotal DECIMAL(12,2) DEFAULT 0,
  discount DECIMAL(12,2) DEFAULT 0,
  shipping DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0,
  currency VARCHAR(5) DEFAULT 'GHS',
  billing_name VARCHAR(200) NULL,
  billing_email VARCHAR(191) NULL,
  billing_phone VARCHAR(50) NULL,
  billing_address TEXT NULL,
  billing_city VARCHAR(120) NULL,
  billing_country VARCHAR(120) DEFAULT 'Ghana',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status(status),
  INDEX idx_user(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NULL,
  product_name VARCHAR(220) NULL,
  quantity INT DEFAULT 1,
  price DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order(order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  reference VARCHAR(100) NULL UNIQUE,
  amount DECIMAL(12,2) DEFAULT 0,
  currency VARCHAR(5) DEFAULT 'GHS',
  gateway VARCHAR(50) DEFAULT 'paystack',
  status VARCHAR(30) DEFAULT 'pending',
  gateway_response LONGTEXT NULL,
  verified_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_reference(reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP PROCEDURE IF EXISTS tg_add_col;
DELIMITER $$
CREATE PROCEDURE tg_add_col(IN p_table VARCHAR(64), IN p_col VARCHAR(64), IN p_def TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_col
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_col, '` ', p_def);
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL tg_add_col('orders','order_number','VARCHAR(80) NULL');
CALL tg_add_col('orders','user_id','INT UNSIGNED NULL');
CALL tg_add_col('orders','status','VARCHAR(30) DEFAULT ''pending''');
CALL tg_add_col('orders','subtotal','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','discount','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','shipping','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','total','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','currency','VARCHAR(5) DEFAULT ''GHS''');
CALL tg_add_col('orders','billing_name','VARCHAR(200) NULL');
CALL tg_add_col('orders','billing_email','VARCHAR(191) NULL');
CALL tg_add_col('orders','billing_phone','VARCHAR(50) NULL');
CALL tg_add_col('orders','billing_address','TEXT NULL');
CALL tg_add_col('orders','billing_city','VARCHAR(120) NULL');
CALL tg_add_col('orders','billing_country','VARCHAR(120) DEFAULT ''Ghana''');
CALL tg_add_col('orders','notes','TEXT NULL');
CALL tg_add_col('order_items','product_name','VARCHAR(220) NULL');
CALL tg_add_col('order_items','quantity','INT DEFAULT 1');
CALL tg_add_col('order_items','price','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('order_items','total','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('payments','reference','VARCHAR(100) NULL');
CALL tg_add_col('payments','amount','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('payments','currency','VARCHAR(5) DEFAULT ''GHS''');
CALL tg_add_col('payments','gateway','VARCHAR(50) DEFAULT ''paystack''');
CALL tg_add_col('payments','status','VARCHAR(30) DEFAULT ''pending''');
CALL tg_add_col('payments','gateway_response','LONGTEXT NULL');
CALL tg_add_col('payments','verified_at','TIMESTAMP NULL');
DROP PROCEDURE IF EXISTS tg_add_col;

CREATE TABLE IF NOT EXISTS activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  details TEXT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_action(action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO activity_logs (action, details) VALUES ('final_order_ui_logo_patch','Final checkout/cart/order UI patch installed');