-- Torvali Platform — MariaDB Schema
-- Run once to initialize the database tables.

CREATE TABLE IF NOT EXISTS merchants (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_name      VARCHAR(100)  UNIQUE NOT NULL COMMENT 'URL slug: /yourstore',
  business_name   VARCHAR(255)  NOT NULL,
  email           VARCHAR(255)  UNIQUE NOT NULL,
  password_hash   VARCHAR(255)  NOT NULL,
  status          ENUM('active','suspended','pending') DEFAULT 'active',
  tagline         TEXT,
  theme           VARCHAR(50)   DEFAULT 'default',
  accent_color    VARCHAR(20)   DEFAULT '#4F46E5',
  hero_heading    VARCHAR(500),
  hero_description TEXT,
  announcement    TEXT,
  logo_url        VARCHAR(500),
  banner_url      VARCHAR(500),
  phone           VARCHAR(50),
  address         TEXT,
  city            VARCHAR(100),
  state           VARCHAR(100),
  country         VARCHAR(100),
  postal_code     VARCHAR(20),
  return_policy          TEXT,
  shipping_policy        TEXT,
  about_us               TEXT,
  stripe_publishable_key VARCHAR(255) DEFAULT NULL,
  stripe_secret_key      VARCHAR(512) DEFAULT NULL,
  moochedda_address      VARCHAR(200) DEFAULT NULL COMMENT 'Merchant public wallet address for receiving digital asset payments',
  moochedda_enabled      TINYINT(1)   DEFAULT 0,
  password_reset_token   VARCHAR(64)  DEFAULT NULL,
  password_reset_expires DATETIME     DEFAULT NULL,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id INT UNSIGNED NOT NULL,
  name        VARCHAR(255) NOT NULL,
  slug        VARCHAR(255) NOT NULL,
  parent_id   INT UNSIGNED DEFAULT NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_id)   REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS products (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id         INT UNSIGNED NOT NULL,
  category_id         INT UNSIGNED DEFAULT NULL,
  title               VARCHAR(500) NOT NULL,
  description         TEXT,
  short_description   TEXT,
  brand               VARCHAR(255),
  manufacturer        VARCHAR(255),
  model_number        VARCHAR(255),
  sku                 VARCHAR(255),
  barcode             VARCHAR(255),
  price               DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  compare_at_price    DECIMAL(10,2),
  cost_per_item       DECIMAL(10,2),
  inventory           INT           DEFAULT 0,
  low_stock_threshold INT           DEFAULT 5,
  weight              DECIMAL(10,3),
  weight_unit         ENUM('kg','g','lb','oz') DEFAULT 'kg',
  length_cm           DECIMAL(10,2),
  width_cm            DECIMAL(10,2),
  height_cm           DECIMAL(10,2),
  material            VARCHAR(500),
  color               VARCHAR(255),
  size                VARCHAR(255),
  country_of_origin   VARCHAR(100),
  status              ENUM('draft','active','archived') DEFAULT 'draft',
  featured            TINYINT(1)    DEFAULT 0,
  tags                TEXT          COMMENT 'comma-separated',
  bullet_points       TEXT          COMMENT 'JSON array',
  warranty            TEXT,
  shipping_info       TEXT,
  return_info         TEXT,
  digital_asset_fee   DECIMAL(10,4) DEFAULT NULL COMMENT 'Deprecated — superseded by digital_asset_price',
  digital_asset_price DECIMAL(10,4) DEFAULT NULL COMMENT 'All-inclusive price in digital asset units (covers product + shipping). Null = not available for MooChedda payment.',
  average_rating      DECIMAL(3,2)  DEFAULT 0.00,
  review_count        INT           DEFAULT 0,
  view_count          INT           DEFAULT 0,
  sold_count          INT           DEFAULT 0,
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (merchant_id)  REFERENCES merchants(id)  ON DELETE CASCADE,
  FOREIGN KEY (category_id)  REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS product_images (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  INT UNSIGNED NOT NULL,
  url         VARCHAR(500) NOT NULL,
  thumbnail_url VARCHAR(500) DEFAULT NULL,
  alt_text    VARCHAR(255),
  position    INT          DEFAULT 0,
  is_primary  TINYINT(1)   DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS product_specifications (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id    INT UNSIGNED NOT NULL,
  spec_name     VARCHAR(255) NOT NULL,
  spec_value    TEXT         NOT NULL,
  display_order INT          DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS product_variants (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id       INT UNSIGNED NOT NULL,
  title            VARCHAR(255) NOT NULL,
  sku              VARCHAR(255),
  price            DECIMAL(10,2),
  compare_at_price DECIMAL(10,2),
  inventory        INT          DEFAULT 0,
  option1_name     VARCHAR(100),
  option1_value    VARCHAR(255),
  option2_name     VARCHAR(100),
  option2_value    VARCHAR(255),
  option3_name     VARCHAR(100),
  option3_value    VARCHAR(255),
  image_url        VARCHAR(500),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS orders (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id      INT UNSIGNED NOT NULL,
  order_number     VARCHAR(50)  UNIQUE NOT NULL,
  customer_name    VARCHAR(255) NOT NULL,
  customer_email   VARCHAR(255),
  customer_phone   VARCHAR(50),
  status           ENUM('new','processing','fulfilled','cancelled','refunded') DEFAULT 'new',
  shipping_status  ENUM('pending','label_created','in_transit','delivered')   DEFAULT 'pending',
  subtotal         DECIMAL(10,2) DEFAULT 0.00,
  shipping_cost    DECIMAL(10,2) DEFAULT 0.00,
  tax              DECIMAL(10,2) DEFAULT 0.00,
  discount         DECIMAL(10,2) DEFAULT 0.00,
  total            DECIMAL(10,2) DEFAULT 0.00,
  shipping_address TEXT,
  billing_address  TEXT,
  notes              TEXT,
  tracking_number    VARCHAR(255),
  payment_intent_id  VARCHAR(255) DEFAULT NULL,
  payment_method     VARCHAR(50)  DEFAULT 'card',
  payment_asset_token VARCHAR(20) DEFAULT NULL,
  payment_status     VARCHAR(50)  DEFAULT 'pending',
  created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS order_items (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED,
  variant_id INT UNSIGNED,
  title      VARCHAR(500)  NOT NULL,
  sku        VARCHAR(255),
  quantity   INT           NOT NULL DEFAULT 1,
  price      DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id)   REFERENCES orders(id)           ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)         ON DELETE SET NULL,
  FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS shipping_zones (
  id                       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id              INT UNSIGNED NOT NULL,
  name                     VARCHAR(255) NOT NULL,
  rate                     DECIMAL(10,2) DEFAULT 0.00,
  free_shipping_threshold  DECIMAL(10,2),
  eta                      VARCHAR(100),
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS customers (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id   INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,
  email         VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customer_store (merchant_id, email),
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS product_reviews (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id       INT UNSIGNED NOT NULL,
  customer_id      INT UNSIGNED DEFAULT NULL,
  reviewer_name    VARCHAR(255) NOT NULL,
  reviewer_email   VARCHAR(255),
  rating           TINYINT      NOT NULL,
  title            VARCHAR(255),
  body             TEXT,
  verified_purchase TINYINT(1)  DEFAULT 0,
  helpful_count    INT          DEFAULT 0,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customer_review (customer_id, product_id),
  FOREIGN KEY (product_id)  REFERENCES products(id)  ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS layouts (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name             VARCHAR(100) NOT NULL,
  slug             VARCHAR(100) UNIQUE NOT NULL,
  description      TEXT,
  designer_name    VARCHAR(100),
  designer_email   VARCHAR(200),
  css_url          VARCHAR(500) NOT NULL,
  preview_image_url VARCHAR(500),
  downloads        INT DEFAULT 0,
  tags             VARCHAR(255),
  status           ENUM('pending','approved','rejected') DEFAULT 'pending',
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pages (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id INT UNSIGNED NOT NULL,
  title       VARCHAR(255) NOT NULL,
  slug        VARCHAR(255) NOT NULL,
  content     LONGTEXT,
  status      ENUM('published','draft') DEFAULT 'draft',
  show_in_nav TINYINT(1) DEFAULT 0,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_merchant_page_slug (merchant_id, slug),
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS press_releases (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(500) NOT NULL,
  slug         VARCHAR(500) UNIQUE NOT NULL,
  excerpt      TEXT,
  content      LONGTEXT,
  status       ENUM('draft','published') DEFAULT 'draft',
  published_at TIMESTAMP NULL DEFAULT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS storefront_sections (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id INT UNSIGNED NOT NULL,
  type        VARCHAR(50)  NOT NULL,
  position    INT          NOT NULL DEFAULT 0,
  settings    LONGTEXT     NOT NULL DEFAULT '{}',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS support_tickets (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  phone      VARCHAR(50),
  subject    VARCHAR(500) NOT NULL,
  message    TEXT NOT NULL,
  status     ENUM('open','in_progress','closed') DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS merchant_email_config (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id  INT UNSIGNED NOT NULL UNIQUE,
  smtp_host    VARCHAR(255) DEFAULT NULL,
  smtp_port    SMALLINT UNSIGNED DEFAULT 587,
  smtp_secure  TINYINT(1) DEFAULT 0,
  smtp_user    VARCHAR(255) DEFAULT NULL,
  smtp_pass    VARCHAR(512) DEFAULT NULL,
  from_name    VARCHAR(255) DEFAULT NULL,
  from_email   VARCHAR(255) DEFAULT NULL,
  enabled      TINYINT(1) DEFAULT 0,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS merchant_email_templates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  merchant_id  INT UNSIGNED NOT NULL,
  type         ENUM('welcome','password_reset','merchant_password_reset','order_pending','order_labeled','order_in_transit','order_delivered') NOT NULL,
  subject      VARCHAR(500) DEFAULT NULL,
  body_html    LONGTEXT DEFAULT NULL,
  enabled      TINYINT(1) DEFAULT 1,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_merchant_template (merchant_id, type),
  FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);
