-- 019_promotion_codes.sql
CREATE TABLE IF NOT EXISTS promotion_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    network_id INT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(64) NOT NULL,
    discount_type ENUM('percent', 'amount') NOT NULL DEFAULT 'percent',
    discount_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    starts_at DATETIME NULL,
    ends_at DATETIME NULL,
    usage_limit INT NULL,
    is_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_promotion_codes_code (code),
    KEY idx_promotion_codes_active_dates (is_active, starts_at, ends_at),
    KEY idx_promotion_codes_network (network_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promotion_code_shops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    promotion_code_id INT NOT NULL,
    shop_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_promotion_code_shops (promotion_code_id, shop_id),
    KEY idx_promotion_code_shops_shop (shop_id),
    CONSTRAINT fk_promotion_code_shops_code FOREIGN KEY (promotion_code_id) REFERENCES promotion_codes(id) ON DELETE CASCADE,
    CONSTRAINT fk_promotion_code_shops_shop FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
