-- =========================================================
-- SQL CAMBIOS V6 — Promociones avanzadas y propuesta pulseras
-- Base de datos: MySQL/MariaDB
-- =========================================================

-- 1) PROMOCIONES AVANZADAS
-- Ejecuta este ALTER solo si tu tabla pos_promotions NO tiene todavía estas columnas.
-- Si alguna columna ya existe, MySQL puede marcar error de columna duplicada; en ese caso omite esa columna.

ALTER TABLE pos_promotions
  ADD COLUMN applies_to VARCHAR(20) NOT NULL DEFAULT 'all' AFTER branch_id,
  ADD COLUMN uses_limit_day INT NULL AFTER uses_limit,
  ADD COLUMN valid_days VARCHAR(30) NULL AFTER expires_at,
  ADD COLUMN starts_at TIME NULL AFTER valid_days,
  ADD COLUMN ends_at TIME NULL AFTER starts_at,
  ADD COLUMN description TEXT NULL AFTER ends_at;

-- 2) PULSERAS — ESTRUCTURA PROPUESTA
-- Esta parte todavía es la base para implementar el módulo de pulseras.
-- Permite inventario de pulseras por sucursal y asignación de pulsera a paquete.

CREATE TABLE IF NOT EXISTS bracelet_colors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id VARCHAR(80) NOT NULL,
  color_name VARCHAR(80) NOT NULL,
  color_hex VARCHAR(20) NULL,
  sku VARCHAR(80) NULL,
  stock INT NOT NULL DEFAULT 0,
  low_stock_alert INT NOT NULL DEFAULT 10,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_bracelet_branch (branch_id),
  INDEX idx_bracelet_active (active),
  CONSTRAINT uq_bracelet_branch_color UNIQUE (branch_id, color_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS package_bracelet_links (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id VARCHAR(80) NOT NULL,
  plan_id INT NOT NULL,
  bracelet_id INT NOT NULL,
  qty_per_package INT NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_branch_plan (branch_id, plan_id),
  INDEX idx_pbl_branch (branch_id),
  INDEX idx_pbl_plan (plan_id),
  INDEX idx_pbl_bracelet (bracelet_id),
  CONSTRAINT fk_pbl_bracelet FOREIGN KEY (bracelet_id) REFERENCES bracelet_colors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bracelet_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bracelet_id INT NOT NULL,
  branch_id VARCHAR(80) NOT NULL,
  type VARCHAR(40) NOT NULL,
  qty_delta INT NOT NULL,
  stock_before INT NOT NULL,
  stock_after INT NOT NULL,
  transaction_id INT NULL,
  user_id INT NULL,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_bm_bracelet (bracelet_id),
  INDEX idx_bm_branch (branch_id),
  INDEX idx_bm_transaction (transaction_id),
  CONSTRAINT fk_bm_bracelet FOREIGN KEY (bracelet_id) REFERENCES bracelet_colors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;