-- PROTOTYPE schema (sandbox MariaDB 10.11). Not host evidence. Gate G0 closed.
CREATE TABLE brands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL, slug VARCHAR(40) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY uq_brands_name (name), UNIQUE KEY uq_brands_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL, name_search VARCHAR(160) NOT NULL,
  phone_normalized CHAR(11) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  phone_first_raw VARCHAR(30) NULL,
  alt_phone_normalized CHAR(11) CHARACTER SET ascii COLLATE ascii_bin NULL,
  default_address TEXT NULL, notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY uq_customers_phone (phone_normalized),
  KEY ix_customers_name (name_search(40)),
  CONSTRAINT ck_customers_phone CHECK (phone_normalized REGEXP '^01[0-9]{9}$')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE order_code_config (
  id TINYINT UNSIGNED NOT NULL PRIMARY KEY,
  issuing_length TINYINT UNSIGNED NOT NULL DEFAULT 6,
  epoch INT UNSIGNED NOT NULL DEFAULT 1,
  claims_frozen TINYINT(1) NOT NULL DEFAULT 0,
  preflight_status VARCHAR(10) NOT NULL DEFAULT 'pending',
  preflight_checked_at DATETIME(6) NULL, flipped_at DATETIME(6) NULL,
  CONSTRAINT ck_occ_id CHECK (id = 1), CONSTRAINT ck_occ_len CHECK (issuing_length IN (6,7))
) ENGINE=InnoDB;
CREATE TABLE order_code_counters (
  code_length TINYINT UNSIGNED NOT NULL PRIMARY KEY,
  range_start INT UNSIGNED NOT NULL, range_end INT UNSIGNED NOT NULL, capacity INT UNSIGNED NOT NULL,
  consumed_count INT UNSIGNED NOT NULL DEFAULT 0, high_water INT UNSIGNED NOT NULL DEFAULT 0,
  warn_pct TINYINT UNSIGNED NOT NULL DEFAULT 70, forced_pct TINYINT UNSIGNED NOT NULL DEFAULT 85
) ENGINE=InnoDB;
CREATE TABLE order_code_journal_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code_length TINYINT UNSIGNED NOT NULL,
  code_count INT UNSIGNED NOT NULL, ack_ref VARCHAR(100) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), UNIQUE KEY uq_ocjb_ack (ack_ref)
) ENGINE=InnoDB;
CREATE TABLE order_codes (
  code VARCHAR(7) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
  code_length TINYINT UNSIGNED NOT NULL, state VARCHAR(12) NOT NULL,
  epoch INT UNSIGNED NULL, batch_id BIGINT UNSIGNED NULL, seq BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL, issued_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  KEY ix_oc_claim (state, code_length, seq),
  CONSTRAINT ck_oc_format CHECK (code REGEXP '^[1-9][0-9]{5,6}$'),
  CONSTRAINT ck_oc_len CHECK (CHAR_LENGTH(code) = code_length),
  CONSTRAINT ck_oc_state CHECK (state IN ('reserved','issued','quarantined','retired'))
) ENGINE=InnoDB;
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_code VARCHAR(7) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  brand_id BIGINT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL,
  recipient_name VARCHAR(150) NOT NULL,
  recipient_phone CHAR(11) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  recipient_address TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'new', payment_status VARCHAR(10) NOT NULL DEFAULT 'unpaid',
  items_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  delivery_charge DECIMAL(12,2) NOT NULL DEFAULT 0,
  order_total DECIMAL(12,2) AS (items_subtotal - discount_amount + delivery_charge) STORED,
  amount_received DECIMAL(12,2) NOT NULL DEFAULT 0,
  balance_due DECIMAL(12,2) AS (order_total - amount_received) STORED,
  dues_amount DECIMAL(12,2) AS (CASE WHEN status IN ('cancelled','returned') THEN 0 ELSE GREATEST(order_total - amount_received, 0) END) STORED,
  credit_amount DECIMAL(12,2) AS (CASE WHEN status IN ('cancelled','returned') THEN GREATEST(amount_received, 0) ELSE GREATEST(amount_received - order_total, 0) END) STORED,
  placed_at DATETIME(6) NOT NULL, created_by BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE KEY uq_orders_code (order_code), UNIQUE KEY uq_orders_id_brand (id, brand_id),
  KEY ix_orders_brand_placed (brand_id, placed_at), KEY ix_orders_brand_status (brand_id, status),
  KEY ix_orders_customer (customer_id),
  CONSTRAINT fk_orders_code FOREIGN KEY (order_code) REFERENCES order_codes (code) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT fk_orders_brand FOREIGN KEY (brand_id) REFERENCES brands (id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT ck_orders_money CHECK (items_subtotal >= 0 AND discount_amount >= 0 AND delivery_charge >= 0 AND items_subtotal - discount_amount + delivery_charge >= 0),
  CONSTRAINT ck_orders_phone CHECK (recipient_phone REGEXP '^01[0-9]{9}$'),
  CONSTRAINT ck_orders_status CHECK (status IN ('new','confirmed','in_production','ready_to_ship','shipped','delivered','on_hold','cancelled','returned')),
  CONSTRAINT ck_orders_pstatus CHECK (payment_status IN ('unpaid','partial','paid','overpaid'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL, brand_id BIGINT UNSIGNED NOT NULL,
  product_name VARCHAR(150) NOT NULL, phone_model_name VARCHAR(120) NOT NULL,
  quantity SMALLINT UNSIGNED NOT NULL, unit_price DECIMAL(12,2) NOT NULL,
  line_total DECIMAL(12,2) AS (quantity * unit_price) STORED,
  customization_text TEXT NULL, position SMALLINT UNSIGNED NOT NULL DEFAULT 0, deleted_at DATETIME(6) NULL,
  KEY ix_items_order (order_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id, brand_id) REFERENCES orders (id, brand_id),
  CONSTRAINT ck_items_qty CHECK (quantity > 0), CONSTRAINT ck_items_price CHECK (unit_price >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE order_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL, brand_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(20) NULL, to_status VARCHAR(20) NOT NULL,
  actor_type VARCHAR(10) NOT NULL DEFAULT 'user', user_id BIGINT UNSIGNED NULL, reason VARCHAR(255) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  KEY ix_hist_order (order_id, created_at),
  CONSTRAINT fk_hist_order FOREIGN KEY (order_id, brand_id) REFERENCES orders (id, brand_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payment_methods (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL, name VARCHAR(60) NOT NULL,
  allow_customer_payment TINYINT(1) NOT NULL DEFAULT 1, allow_remittance_deposit TINYINT(1) NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_pm_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL, brand_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(10) NOT NULL, amount DECIMAL(12,2) NOT NULL, method_id BIGINT UNSIGNED NOT NULL,
  reference VARCHAR(100) NULL, paid_at DATETIME(6) NOT NULL,
  recorded_by BIGINT UNSIGNED NULL, recorded_via VARCHAR(16) NOT NULL DEFAULT 'staff', note VARCHAR(255) NULL,
  voided_at DATETIME(6) NULL, voided_by BIGINT UNSIGNED NULL, void_reason VARCHAR(255) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  active_ref_key VARCHAR(160) AS (CASE WHEN reference IS NOT NULL AND voided_at IS NULL THEN CONCAT(type, ':', method_id, ':', reference) ELSE NULL END) STORED,
  UNIQUE KEY uq_payments_active_ref (active_ref_key),
  KEY ix_payments_order (order_id), KEY ix_payments_brand_paid (brand_id, paid_at),
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id, brand_id) REFERENCES orders (id, brand_id),
  CONSTRAINT fk_payments_method FOREIGN KEY (method_id) REFERENCES payment_methods (id),
  CONSTRAINT ck_payments_amount CHECK (amount > 0), CONSTRAINT ck_payments_type CHECK (type IN ('payment','refund'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;