-- =====================================================================
--  Atelier — schema MySQL 8 / MariaDB 10.6+
--  Toate sumele sunt INT în unități minore ale monedei de bază (pence).
--  Textele multilingve (ro/en) sunt coloane separate: name_ro / name_en.
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Administrare ----------
CREATE TABLE admin_users (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(120) NOT NULL,
  email           VARCHAR(190) NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  role            ENUM('owner','manager','staff') NOT NULL DEFAULT 'staff',
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  totp_enabled    TINYINT(1) NOT NULL DEFAULT 0,
  totp_secret     VARCHAR(255) NULL,
  recovery_codes  TEXT NULL,
  failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until    DATETIME NULL,
  last_login_at   DATETIME NULL,
  last_login_ip   VARCHAR(45) NULL,
  locale          CHAR(2) NOT NULL DEFAULT 'ro',
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  `key`       VARCHAR(120) NOT NULL,
  `value`     MEDIUMTEXT NULL,
  is_secret   TINYINT(1) NOT NULL DEFAULT 0,
  updated_at  DATETIME NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_log (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_user_id INT UNSIGNED NULL,
  action        VARCHAR(80) NOT NULL,
  entity        VARCHAR(60) NULL,
  entity_id     BIGINT UNSIGNED NULL,
  meta          JSON NULL,
  ip            VARCHAR(45) NULL,
  created_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_audit_created (created_at),
  KEY ix_audit_entity (entity, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_log (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  to_email   VARCHAR(190) NOT NULL,
  subject    VARCHAR(255) NOT NULL,
  template   VARCHAR(80) NOT NULL,
  status     ENUM('sent','failed') NOT NULL,
  error      TEXT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_email_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Catalog ----------
CREATE TABLE categories (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id   INT UNSIGNED NULL,
  slug        VARCHAR(160) NOT NULL,
  name_ro     VARCHAR(160) NOT NULL,
  name_en     VARCHAR(160) NOT NULL,
  description_ro TEXT NULL,
  description_en TEXT NULL,
  image_url   VARCHAR(255) NULL,
  position    INT NOT NULL DEFAULT 0,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  meta_title_ro VARCHAR(190) NULL, meta_title_en VARCHAR(190) NULL,
  meta_description_ro VARCHAR(320) NULL, meta_description_en VARCHAR(320) NULL,
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cat_slug (slug),
  KEY ix_cat_parent (parent_id),
  CONSTRAINT fk_cat_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku             VARCHAR(64) NULL,
  slug            VARCHAR(190) NOT NULL,
  name_ro         VARCHAR(190) NOT NULL,
  name_en         VARCHAR(190) NOT NULL,
  short_ro        VARCHAR(320) NULL,
  short_en        VARCHAR(320) NULL,
  description_ro  MEDIUMTEXT NULL,
  description_en  MEDIUMTEXT NULL,
  care_ro         TEXT NULL,
  care_en         TEXT NULL,
  price           INT NOT NULL DEFAULT 0,             -- preț de bază (minor units)
  compare_at_price INT NULL,                           -- preț tăiat
  cost_price      INT NULL,                            -- cost intern (marjă)
  stock_mode      ENUM('track','made_to_order','unlimited') NOT NULL DEFAULT 'made_to_order',
  stock_qty       INT NOT NULL DEFAULT 0,
  low_stock_threshold INT NOT NULL DEFAULT 3,
  lead_time_days_min TINYINT UNSIGNED NOT NULL DEFAULT 5,
  lead_time_days_max TINYINT UNSIGNED NOT NULL DEFAULT 7,
  weight_grams    INT NULL,
  is_active       TINYINT(1) NOT NULL DEFAULT 0,
  is_featured     TINYINT(1) NOT NULL DEFAULT 0,
  badge_ro        VARCHAR(40) NULL, badge_en VARCHAR(40) NULL,    -- „Bestseller”, „Nou”
  allow_reference_upload TINYINT(1) NOT NULL DEFAULT 1,
  allow_gift_wrap TINYINT(1) NOT NULL DEFAULT 1,
  gift_wrap_price INT NOT NULL DEFAULT 0,
  position        INT NOT NULL DEFAULT 0,
  rating_avg      DECIMAL(3,2) NOT NULL DEFAULT 0,
  rating_count    INT NOT NULL DEFAULT 0,
  meta_title_ro   VARCHAR(190) NULL, meta_title_en VARCHAR(190) NULL,
  meta_description_ro VARCHAR(320) NULL, meta_description_en VARCHAR(320) NULL,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_prod_slug (slug),
  KEY ix_prod_active (is_active, position),
  KEY ix_prod_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_categories (
  product_id  INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, category_id),
  CONSTRAINT fk_pc_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_images (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id  INT UNSIGNED NOT NULL,
  url         VARCHAR(255) NOT NULL,          -- original webp
  url_thumb   VARCHAR(255) NULL,
  url_medium  VARCHAR(255) NULL,
  url_large   VARCHAR(255) NULL,
  alt_ro      VARCHAR(190) NULL,
  alt_en      VARCHAR(190) NULL,
  position    INT NOT NULL DEFAULT 0,
  is_primary  TINYINT(1) NOT NULL DEFAULT 0,
  -- zona în care se desenează previzualizarea gravurii (procente din imagine)
  preview_x   DECIMAL(5,2) NULL, preview_y DECIMAL(5,2) NULL,
  preview_w   DECIMAL(5,2) NULL, preview_rotation DECIMAL(5,2) NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_img_product (product_id, position),
  CONSTRAINT fk_img_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Opțiuni de personalizare per produs. Tipuri:
--   select  : metal / mărime / lungime  (valori în product_option_values, fiecare cu diferență de preț)
--   swatch  : ca select, dar afișat cu culoare
--   text    : gravură / inițiale / mesaj (max_length, fonturi permise, preț per opțiune)
--   font    : alegerea fontului gravurii
--   upload  : imagine de referință
--   checkbox: ex. „cutie cadou”
CREATE TABLE product_options (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id   INT UNSIGNED NOT NULL,
  type         ENUM('select','swatch','text','font','upload','checkbox') NOT NULL,
  code         VARCHAR(40) NOT NULL,            -- ex: metal, size, engraving
  label_ro     VARCHAR(120) NOT NULL,
  label_en     VARCHAR(120) NOT NULL,
  help_ro      VARCHAR(255) NULL, help_en VARCHAR(255) NULL,
  is_required  TINYINT(1) NOT NULL DEFAULT 0,
  price_delta  INT NOT NULL DEFAULT 0,          -- pentru text/upload/checkbox: cost fix când e completat
  max_length   SMALLINT UNSIGNED NULL,          -- pentru text
  pattern      VARCHAR(120) NULL,               -- regex permis (ex. doar litere)
  affects_preview TINYINT(1) NOT NULL DEFAULT 0, -- textul apare în previzualizarea live
  position     INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_opt_product (product_id, position),
  CONSTRAINT fk_opt_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_option_values (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  option_id    INT UNSIGNED NOT NULL,
  code         VARCHAR(40) NOT NULL,            -- ex: gold-14k
  label_ro     VARCHAR(120) NOT NULL,
  label_en     VARCHAR(120) NOT NULL,
  price_delta  INT NOT NULL DEFAULT 0,
  swatch_hex   CHAR(7) NULL,
  image_id     INT UNSIGNED NULL,               -- schimbă imaginea principală când e selectat
  font_family  VARCHAR(120) NULL,               -- pentru type=font
  stock_qty    INT NULL,                        -- stoc per variantă (NULL = folosește stocul produsului)
  is_default   TINYINT(1) NOT NULL DEFAULT 0,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  position     INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_val_option (option_id, position),
  CONSTRAINT fk_val_option FOREIGN KEY (option_id) REFERENCES product_options(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_related (
  product_id  INT UNSIGNED NOT NULL,
  related_id  INT UNSIGNED NOT NULL,
  position    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id, related_id),
  CONSTRAINT fk_rel_p FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_rel_r FOREIGN KEY (related_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Clienți ----------
CREATE TABLE customers (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email           VARCHAR(190) NOT NULL,
  password_hash   VARCHAR(255) NULL,            -- NULL = guest
  first_name      VARCHAR(80) NULL,
  last_name       VARCHAR(80) NULL,
  phone           VARCHAR(40) NULL,
  locale          CHAR(2) NOT NULL DEFAULT 'ro',
  currency        CHAR(3) NOT NULL DEFAULT 'GBP',
  accepts_marketing TINYINT(1) NOT NULL DEFAULT 0,
  marketing_consent_at DATETIME NULL,
  notes           TEXT NULL,                    -- note interne (admin)
  tags            VARCHAR(255) NULL,            -- „vip,retur”
  orders_count    INT NOT NULL DEFAULT 0,
  total_spent     BIGINT NOT NULL DEFAULT 0,
  last_order_at   DATETIME NULL,
  email_verified_at DATETIME NULL,
  remember_token  VARCHAR(100) NULL,
  reset_token     VARCHAR(100) NULL,
  reset_expires_at DATETIME NULL,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cust_email (email),
  KEY ix_cust_last_order (last_order_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_addresses (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  label       VARCHAR(60) NULL,
  first_name  VARCHAR(80) NOT NULL,
  last_name   VARCHAR(80) NOT NULL,
  company     VARCHAR(120) NULL,
  line1       VARCHAR(190) NOT NULL,
  line2       VARCHAR(190) NULL,
  city        VARCHAR(120) NOT NULL,
  region      VARCHAR(120) NULL,
  postcode    VARCHAR(20) NOT NULL,
  country     CHAR(2) NOT NULL,
  phone       VARCHAR(40) NULL,
  is_default  TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_addr_customer (customer_id),
  CONSTRAINT fk_addr_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wishlists (
  customer_id INT UNSIGNED NOT NULL,
  product_id  INT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (customer_id, product_id),
  CONSTRAINT fk_wl_c FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_wl_p FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_alerts (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id  INT UNSIGNED NOT NULL,
  email       VARCHAR(190) NOT NULL,
  locale      CHAR(2) NOT NULL DEFAULT 'ro',
  notified_at DATETIME NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_sa_product (product_id, notified_at),
  CONSTRAINT fk_sa_p FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Livrare ----------
CREATE TABLE shipping_zones (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(120) NOT NULL,             -- „Regatul Unit”, „România”, „UE”
  countries  TEXT NOT NULL,                     -- JSON array de coduri ISO: ["GB"], ["RO"], ["DE","FR",...]
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  position   INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE shipping_rates (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  zone_id         INT UNSIGNED NOT NULL,
  name_ro         VARCHAR(120) NOT NULL,        -- „Curier standard”
  name_en         VARCHAR(120) NOT NULL,
  carrier         VARCHAR(60) NULL,             -- DHL, Royal Mail, Fan Courier
  price           INT NOT NULL DEFAULT 0,
  free_over       INT NULL,                     -- gratuit peste suma X
  min_days        TINYINT UNSIGNED NULL,
  max_days        TINYINT UNSIGNED NULL,
  requires_signature TINYINT(1) NOT NULL DEFAULT 1,
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  position        INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_rate_zone (zone_id),
  CONSTRAINT fk_rate_zone FOREIGN KEY (zone_id) REFERENCES shipping_zones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Promoții ----------
CREATE TABLE promo_codes (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code            VARCHAR(40) NOT NULL,
  description     VARCHAR(190) NULL,
  type            ENUM('percent','fixed','free_shipping') NOT NULL,
  value           INT NOT NULL DEFAULT 0,       -- procent (ex. 10) sau sumă fixă (minor units)
  min_subtotal    INT NULL,
  max_discount    INT NULL,
  applies_to      ENUM('all','categories','products') NOT NULL DEFAULT 'all',
  applies_ids     TEXT NULL,                    -- JSON array de id-uri
  usage_limit     INT NULL,
  usage_per_customer INT NULL DEFAULT 1,
  used_count      INT NOT NULL DEFAULT 0,
  first_order_only TINYINT(1) NOT NULL DEFAULT 0,
  starts_at       DATETIME NULL,
  ends_at         DATETIME NULL,
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_promo_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE gift_cards (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code          VARCHAR(40) NOT NULL,
  initial_value INT NOT NULL,
  balance       INT NOT NULL,
  currency      CHAR(3) NOT NULL DEFAULT 'GBP',
  recipient_email VARCHAR(190) NULL,
  recipient_name VARCHAR(120) NULL,
  message       TEXT NULL,
  order_id      INT UNSIGNED NULL,
  expires_at    DATE NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_gc_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Comenzi ----------
CREATE TABLE orders (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  number           VARCHAR(30) NOT NULL,
  customer_id      INT UNSIGNED NULL,
  email            VARCHAR(190) NOT NULL,
  phone            VARCHAR(40) NULL,
  locale           CHAR(2) NOT NULL DEFAULT 'ro',
  currency         CHAR(3) NOT NULL DEFAULT 'GBP',   -- moneda afișată clientului
  exchange_rate    DECIMAL(12,6) NOT NULL DEFAULT 1,  -- rata față de moneda de bază la momentul comenzii
  -- sume în moneda de bază (minor units)
  subtotal         INT NOT NULL DEFAULT 0,
  discount_total   INT NOT NULL DEFAULT 0,
  shipping_total   INT NOT NULL DEFAULT 0,
  tax_total        INT NOT NULL DEFAULT 0,
  gift_card_total  INT NOT NULL DEFAULT 0,
  grand_total      INT NOT NULL DEFAULT 0,
  promo_code_id    INT UNSIGNED NULL,
  promo_code       VARCHAR(40) NULL,
  gift_card_id     INT UNSIGNED NULL,
  -- stări
  status           ENUM('pending','confirmed','in_production','quality_check','ready','shipped','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending',
  payment_status   ENUM('unpaid','awaiting_transfer','paid','partially_refunded','refunded','failed') NOT NULL DEFAULT 'unpaid',
  payment_method   ENUM('stripe','bank_transfer','gift_card','manual') NOT NULL DEFAULT 'stripe',
  payment_ref      VARCHAR(190) NULL,                 -- Stripe PaymentIntent / referință transfer
  paid_at          DATETIME NULL,
  -- livrare
  shipping_rate_id INT UNSIGNED NULL,
  shipping_method  VARCHAR(120) NULL,
  carrier          VARCHAR(60) NULL,
  tracking_number  VARCHAR(120) NULL,
  tracking_url     VARCHAR(255) NULL,
  shipped_at       DATETIME NULL,
  delivered_at     DATETIME NULL,
  due_date         DATE NULL,                         -- termen intern de finalizare
  requested_delivery_date DATE NULL,                  -- clientul vrea până la o dată anume
  -- adrese (snapshot JSON: nume, linie1, oraș, cod, țară, telefon)
  shipping_address JSON NOT NULL,
  billing_address  JSON NULL,
  -- cadou
  is_gift          TINYINT(1) NOT NULL DEFAULT 0,
  gift_message     TEXT NULL,
  hide_prices      TINYINT(1) NOT NULL DEFAULT 0,
  customer_note    TEXT NULL,
  internal_note    TEXT NULL,
  -- marketing attribution
  utm_source VARCHAR(120) NULL, utm_medium VARCHAR(120) NULL, utm_campaign VARCHAR(190) NULL,
  fbclid VARCHAR(255) NULL, gclid VARCHAR(255) NULL, ttclid VARCHAR(255) NULL,
  fbp VARCHAR(120) NULL, fbc VARCHAR(255) NULL,
  ip               VARCHAR(45) NULL,
  user_agent       VARCHAR(255) NULL,
  -- tracking server-side (CAPI/Ads) trimis?
  conversion_sent_at DATETIME NULL,
  cancelled_at     DATETIME NULL,
  cancel_reason    VARCHAR(255) NULL,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_order_number (number),
  KEY ix_order_customer (customer_id),
  KEY ix_order_status (status, created_at),
  KEY ix_order_payment (payment_status),
  KEY ix_order_created (created_at),
  CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id       INT UNSIGNED NOT NULL,
  product_id     INT UNSIGNED NULL,
  sku            VARCHAR(64) NULL,
  name           VARCHAR(190) NOT NULL,              -- snapshot în limba comenzii
  image_url      VARCHAR(255) NULL,
  qty            SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  unit_price     INT NOT NULL,                       -- preț de bază unitar
  options_total  INT NOT NULL DEFAULT 0,             -- suma diferențelor din personalizare
  line_total     INT NOT NULL,                       -- (unit_price + options_total) * qty
  -- personalizarea: JSON [{code, label, value, value_label, price_delta, font?, upload?}]
  personalization JSON NULL,
  reference_file VARCHAR(255) NULL,                  -- cale privată (storage/private/...)
  production_status ENUM('waiting','approved','in_progress','done') NOT NULL DEFAULT 'waiting',
  production_note TEXT NULL,
  PRIMARY KEY (id),
  KEY ix_item_order (order_id),
  CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_events (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id     INT UNSIGNED NOT NULL,
  admin_user_id INT UNSIGNED NULL,
  type         VARCHAR(40) NOT NULL,                 -- status_changed, note, email_sent, payment, refund, tracking
  message      TEXT NULL,
  meta         JSON NULL,
  is_customer_visible TINYINT(1) NOT NULL DEFAULT 0,
  created_at   DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_ev_order (order_id, created_at),
  CONSTRAINT fk_ev_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_refunds (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id    INT UNSIGNED NOT NULL,
  amount      INT NOT NULL,
  reason      VARCHAR(255) NULL,
  provider_ref VARCHAR(190) NULL,
  created_by  INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_ref_order (order_id),
  CONSTRAINT fk_ref_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Coșuri (persistente, pentru recuperare coș abandonat)
CREATE TABLE carts (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  token         VARCHAR(64) NOT NULL,
  customer_id   INT UNSIGNED NULL,
  email         VARCHAR(190) NULL,
  locale        CHAR(2) NOT NULL DEFAULT 'ro',
  currency      CHAR(3) NOT NULL DEFAULT 'GBP',
  items         JSON NOT NULL,                     -- [{product_id, qty, options:{...}, reference_file}]
  promo_code    VARCHAR(40) NULL,
  subtotal      INT NOT NULL DEFAULT 0,
  recovery_sent_at DATETIME NULL,
  recovered_order_id INT UNSIGNED NULL,
  utm_source VARCHAR(120) NULL, utm_medium VARCHAR(120) NULL, utm_campaign VARCHAR(190) NULL,
  fbclid VARCHAR(255) NULL, gclid VARCHAR(255) NULL, ttclid VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cart_token (token),
  KEY ix_cart_updated (updated_at),
  KEY ix_cart_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Recenzii ----------
CREATE TABLE reviews (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id   INT UNSIGNED NOT NULL,
  order_id     INT UNSIGNED NULL,
  customer_id  INT UNSIGNED NULL,
  author_name  VARCHAR(120) NOT NULL,
  author_location VARCHAR(120) NULL,
  rating       TINYINT UNSIGNED NOT NULL,
  title        VARCHAR(190) NULL,
  body         TEXT NOT NULL,
  locale       CHAR(2) NOT NULL DEFAULT 'ro',
  images       JSON NULL,
  is_verified  TINYINT(1) NOT NULL DEFAULT 0,
  status       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  reply        TEXT NULL,
  replied_at   DATETIME NULL,
  created_at   DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_rev_product (product_id, status),
  CONSTRAINT fk_rev_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Conținut ----------
CREATE TABLE pages (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug        VARCHAR(160) NOT NULL,               -- despre-noi, livrare, retur, termeni, confidentialitate, contact
  title_ro    VARCHAR(190) NOT NULL, title_en VARCHAR(190) NOT NULL,
  body_ro     MEDIUMTEXT NULL, body_en MEDIUMTEXT NULL,
  meta_title_ro VARCHAR(190) NULL, meta_title_en VARCHAR(190) NULL,
  meta_description_ro VARCHAR(320) NULL, meta_description_en VARCHAR(320) NULL,
  show_in_footer TINYINT(1) NOT NULL DEFAULT 1,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  position    INT NOT NULL DEFAULT 0,
  updated_at  DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_page_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE posts (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug        VARCHAR(190) NOT NULL,
  title_ro    VARCHAR(190) NOT NULL, title_en VARCHAR(190) NOT NULL,
  excerpt_ro  VARCHAR(320) NULL, excerpt_en VARCHAR(320) NULL,
  body_ro     MEDIUMTEXT NULL, body_en MEDIUMTEXT NULL,
  cover_url   VARCHAR(255) NULL,
  category    VARCHAR(60) NULL,                     -- ghid, îngrijire, cadouri, atelier
  reading_minutes TINYINT UNSIGNED NULL,
  meta_title_ro VARCHAR(190) NULL, meta_title_en VARCHAR(190) NULL,
  meta_description_ro VARCHAR(320) NULL, meta_description_en VARCHAR(320) NULL,
  status      ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at DATETIME NULL,
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_post_slug (slug),
  KEY ix_post_pub (status, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE newsletter_subscribers (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email       VARCHAR(190) NOT NULL,
  locale      CHAR(2) NOT NULL DEFAULT 'ro',
  source      VARCHAR(60) NULL,                     -- footer, checkout, popup
  discount_code VARCHAR(40) NULL,
  confirmed_at DATETIME NULL,
  unsubscribed_at DATETIME NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_nl_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contact_messages (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(120) NOT NULL,
  email      VARCHAR(190) NOT NULL,
  phone      VARCHAR(40) NULL,
  subject    VARCHAR(190) NULL,
  message    TEXT NOT NULL,
  order_number VARCHAR(30) NULL,
  status     ENUM('new','replied','closed') NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_cm_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Evenimente de marketing trimise server-side (CAPI, Google Ads, TikTok)
CREATE TABLE tracking_events (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider    VARCHAR(30) NOT NULL,                 -- meta, ga4, tiktok
  event_name  VARCHAR(60) NOT NULL,
  event_id    VARCHAR(80) NOT NULL,                 -- pentru deduplicare pixel/server
  order_id    INT UNSIGNED NULL,
  payload     JSON NULL,
  response    TEXT NULL,
  status      ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  created_at  DATETIME NOT NULL,
  sent_at     DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_te_status (status, created_at),
  KEY ix_te_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;