-- ============================================================
-- Shopify Product Configurator - Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS ardry CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ardry;

-- ============================================================
-- Attribute Groups (for organizing attributes)
-- ============================================================
CREATE TABLE IF NOT EXISTS attribute_groups (
  id INT PRIMARY KEY AUTO_INCREMENT,
  shop_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT DEFAULT NULL,
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
);

-- ============================================================
-- Attributes (custom option definitions)
-- ============================================================
CREATE TABLE IF NOT EXISTS attributes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  shop_id INT NOT NULL,
  group_id INT DEFAULT NULL,
  name VARCHAR(255) NOT NULL COMMENT 'Internal name/key',
  label VARCHAR(255) NOT NULL COMMENT 'Display label shown to customer',
  type ENUM(
    'text',
    'textarea',
    'number',
    'dropdown',
    'radio',
    'checkbox',
    'multiselect',
    'list',
    'date',
    'color',
    'file',
    'toggle'
  ) NOT NULL DEFAULT 'text',
  placeholder VARCHAR(500) DEFAULT NULL,
  default_value TEXT DEFAULT NULL,
  help_text TEXT DEFAULT NULL COMMENT 'Helper text shown below the field',
  is_required BOOLEAN DEFAULT FALSE,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INT DEFAULT 0,
  validation_rules JSON DEFAULT NULL COMMENT 'e.g. {"min":1,"max":100,"pattern":"^[A-Z]+$"}',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  FOREIGN KEY (group_id) REFERENCES attribute_groups(id) ON DELETE SET NULL
);

-- ============================================================
-- Attribute Options (choices for dropdown/radio/checkbox/list)
-- ============================================================
CREATE TABLE IF NOT EXISTS attribute_options (
  id INT PRIMARY KEY AUTO_INCREMENT,
  attribute_id INT NOT NULL,
  label VARCHAR(255) NOT NULL COMMENT 'Display text',
  value VARCHAR(255) NOT NULL COMMENT 'Stored value',
  price_modifier DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Additional price for this option',
  color_swatch VARCHAR(7) DEFAULT NULL COMMENT 'Hex color for color type',
  image_url VARCHAR(1000) DEFAULT NULL COMMENT 'Image for visual options',
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON DELETE CASCADE
);

-- ============================================================
-- Product Attributes (which attributes are linked to which products)
-- ============================================================
CREATE TABLE IF NOT EXISTS product_attributes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  shop_id INT NOT NULL,
  product_id VARCHAR(100) NOT NULL COMMENT 'Shopify product GID or numeric ID',
  product_title VARCHAR(500) DEFAULT NULL COMMENT 'Cached product title',
  attribute_id INT NOT NULL,
  sort_order INT DEFAULT 0,
  is_required BOOLEAN DEFAULT FALSE COMMENT 'Override required at product level',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_product_attribute (product_id, attribute_id),
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON DELETE CASCADE
);

-- ============================================================
-- Order Line Item Properties (captured configurator selections)
-- ============================================================
CREATE TABLE IF NOT EXISTS order_configurations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  shop_id INT NOT NULL,
  order_id VARCHAR(100) NOT NULL COMMENT 'Shopify order ID',
  order_name VARCHAR(100) DEFAULT NULL COMMENT 'e.g. #1001',
  line_item_id VARCHAR(100) NOT NULL COMMENT 'Shopify line item ID',
  product_id VARCHAR(100) NOT NULL,
  variant_id VARCHAR(100) DEFAULT NULL,
  configurations JSON NOT NULL COMMENT 'Array of {attribute_id, label, value} objects',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
);

-- ============================================================
-- Indexes for performance
-- ============================================================
CREATE INDEX idx_attributes_shop ON attributes(shop_id);
CREATE INDEX idx_attributes_group ON attributes(group_id);
CREATE INDEX idx_product_attributes_product ON product_attributes(product_id);
CREATE INDEX idx_product_attributes_shop ON product_attributes(shop_id);
CREATE INDEX idx_order_configurations_order ON order_configurations(order_id);

INSERT INTO attribute_groups (shop_id, name, description, sort_order) VALUES
(1, 'Personalization', 'Custom text and personalization options', 1),
(1, 'Material & Finish', 'Material type and finish options', 2),
(1, 'Size & Dimensions', 'Size and measurement options', 3);

INSERT INTO attributes (shop_id, group_id, name, label, type, placeholder, help_text, is_required, sort_order) VALUES
(1, 1, 'custom_text', 'Custom Engraving Text', 'text', 'Enter your text here...', 'Max 50 characters', FALSE, 1),
(1, 1, 'gift_message', 'Gift Message', 'textarea', 'Write your gift message...', 'This will be printed on the gift card', FALSE, 2),
(1, 2, 'material', 'Material', 'dropdown', NULL, 'Select the material type', TRUE, 1),
(1, 2, 'finish', 'Finish Type', 'radio', NULL, 'Choose your preferred finish', TRUE, 2),
(1, 3, 'size', 'Size', 'dropdown', NULL, 'Select your size', TRUE, 1),
(1, 1, 'color_preference', 'Color Preference', 'color', NULL, 'Pick your preferred color', FALSE, 3),
(1, 1, 'add_ons', 'Add-ons', 'checkbox', NULL, 'Select any additional options', FALSE, 4);

INSERT INTO attribute_options (attribute_id, label, value, price_modifier, sort_order) VALUES
-- Material options
(3, 'Stainless Steel', 'stainless_steel', 0.00, 1),
(3, 'Brass', 'brass', 5.00, 2),
(3, 'Gold Plated', 'gold_plated', 15.00, 3),
(3, 'Silver Plated', 'silver_plated', 10.00, 4),
-- Finish options
(4, 'Matte', 'matte', 0.00, 1),
(4, 'Glossy', 'glossy', 2.00, 2),
(4, 'Brushed', 'brushed', 3.00, 3),
-- Size options
(5, 'Small (S)', 'S', 0.00, 1),
(5, 'Medium (M)', 'M', 0.00, 2),
(5, 'Large (L)', 'L', 0.00, 3),
(5, 'Extra Large (XL)', 'XL', 5.00, 4),
-- Add-ons (checkbox)
(7, 'Gift Wrapping', 'gift_wrap', 3.99, 1),
(7, 'Rush Processing', 'rush', 9.99, 2),
(7, 'Extended Warranty', 'warranty', 14.99, 3);
