-- GuaranteeApp — اسکیمای MySQL (منبع: MD files/GuaranteeApp-01-Database.md §۲)
-- Import با utf8mb4 انجام شود (phpMyAdmin → Import).
-- ۱۱ جدول. همهٔ زمان‌ها را PHP با Asia/Tehran درج می‌کند (NOW()/CURDATE() استفاده نشود).

SET NAMES utf8mb4;

CREATE TABLE option_families (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  local_uuid    CHAR(36)   NOT NULL UNIQUE,
  family_number TINYINT UNSIGNED NOT NULL UNIQUE,        -- رقم اول کد، ۱ تا ۹
  name          VARCHAR(100) NOT NULL,
  created_at    DATETIME   NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE device_options (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  local_uuid  CHAR(36)   NOT NULL UNIQUE,
  family_id   INT        NOT NULL,
  value_index TINYINT UNSIGNED NOT NULL,                 -- رقم دوم کد، ۱ تا ۹
  code        CHAR(2)    NOT NULL,                       -- family_number || value_index
  name        VARCHAR(100) NOT NULL,
  is_locked   TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME   NOT NULL,
  UNIQUE KEY uq_family_value (family_id, value_index),
  FOREIGN KEY (family_id) REFERENCES option_families(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_models (
  id                   INT AUTO_INCREMENT PRIMARY KEY,
  local_uuid           CHAR(36)    NOT NULL UNIQUE,
  name                 VARCHAR(150) NOT NULL,
  base_code            VARCHAR(20)  NOT NULL,
  part_number_code     VARCHAR(40)  NOT NULL UNIQUE,     -- base_code[-کدهای خانواده]
  is_locked            TINYINT(1)   NOT NULL DEFAULT 0,
  warranty_months      SMALLINT UNSIGNED NOT NULL,       -- ۱ تا ۱۲۰ (اعتبارسنجی در API)
  warranty_label_text  VARCHAR(60)  NOT NULL,            -- مثلاً "1 Year Warranty"
  created_at           DATETIME     NOT NULL,
  synced_at            DATETIME     NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_model_options (
  product_model_id INT NOT NULL,
  device_option_id INT NOT NULL,
  PRIMARY KEY (product_model_id, device_option_id),
  FOREIGN KEY (product_model_id) REFERENCES product_models(id),
  FOREIGN KEY (device_option_id) REFERENCES device_options(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE production_batches (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  local_uuid       CHAR(36)    NOT NULL UNIQUE,
  product_model_id INT         NOT NULL,
  batch_code       VARCHAR(30) NOT NULL UNIQUE,          -- 260923-B1
  quantity         INT         NOT NULL,
  created_at       DATETIME    NOT NULL,
  FOREIGN KEY (product_model_id) REFERENCES product_models(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
  id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
  serial_number       VARCHAR(64) NOT NULL UNIQUE,       -- 40 + 1 + 6 + 1 + 4 = 52 ≤ 64
  product_model_id    INT         NOT NULL,
  part_number_code    VARCHAR(40) NOT NULL,              -- denormalized
  production_batch_id INT         NOT NULL,
  production_date     DATE        NOT NULL,
  label1_printed_at   DATETIME    NULL,
  label2_printed_at   DATETIME    NULL,
  created_at          DATETIME    NOT NULL,
  synced_at           DATETIME    NOT NULL,
  KEY idx_prod_date_model (production_date, product_model_id),
  FOREIGN KEY (product_model_id)    REFERENCES product_models(id),
  FOREIGN KEY (production_batch_id) REFERENCES production_batches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE warehouse_events (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  serial_number VARCHAR(64) NOT NULL,
  product_id    BIGINT      NULL,                        -- NULL تا وقتی محصول واقعی سینک شود
  event_type    ENUM('packaging','warehouse_exit') NOT NULL,
  scanned_at    DATETIME    NOT NULL,
  is_orphan     TINYINT(1)  NOT NULL DEFAULT 0,
  matched_at    DATETIME    NULL,
  UNIQUE KEY dup_guard (serial_number, event_type),
  KEY idx_event_time (event_type, scanned_at),
  KEY idx_product (product_id),
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE warranty_activations (
  id             BIGINT AUTO_INCREMENT PRIMARY KEY,
  serial_number  VARCHAR(64) NOT NULL UNIQUE,
  product_id     BIGINT      NULL,
  activated_at   DATETIME    NOT NULL,
  admin_notified TINYINT(1)  NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE telegram_admins (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  chat_id VARCHAR(30)  NOT NULL UNIQUE,
  name    VARCHAR(100) NOT NULL,
  active  TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_keys (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  key_hash      CHAR(64)     NOT NULL UNIQUE,            -- SHA-256 هگز؛ نه متن خام
  workshop_name VARCHAR(100) NOT NULL,
  active        TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    DATETIME    NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE label_template_backups (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  label_type     TINYINT      NOT NULL,                  -- 1 یا 2
  profile_name   VARCHAR(100) NOT NULL,
  file_name      VARCHAR(150) NOT NULL,
  content_hash   CHAR(64)     NOT NULL,
  print_warranty TINYINT(1)   NOT NULL DEFAULT 0,
  btw_blob       MEDIUMBLOB   NOT NULL,
  updated_at     DATETIME     NOT NULL,
  UNIQUE KEY uq_type_profile (label_type, profile_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;