-- FASE 10 - cupons, taxas e agendamento
CREATE TABLE IF NOT EXISTS coupons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(50) NOT NULL UNIQUE,
 description VARCHAR(255) NULL,
 discount_type ENUM('PERCENT','FIXED') NOT NULL DEFAULT 'FIXED',
 discount_value DECIMAL(10,2) NOT NULL,
 min_order DECIMAL(10,2) NOT NULL DEFAULT 0,
 max_discount DECIMAL(10,2) NULL,
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 usage_limit INT NULL,
 used_count INT NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_zones (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 match_type ENUM('DISTRICT','CEP_PREFIX') NOT NULL DEFAULT 'DISTRICT',
 match_value VARCHAR(120) NOT NULL,
 fee DECIMAL(10,2) NOT NULL DEFAULT 0,
 min_order DECIMAL(10,2) NOT NULL DEFAULT 0,
 estimated_minutes INT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE orders
 ADD COLUMN IF NOT EXISTS coupon_code VARCHAR(50) NULL AFTER discount,
 ADD COLUMN IF NOT EXISTS scheduled_for DATETIME NULL AFTER notes;

INSERT INTO delivery_zones(name,match_type,match_value,fee,min_order,estimated_minutes,active)
SELECT 'Valença Centro','DISTRICT','Centro',8.00,0,45,1
WHERE NOT EXISTS (SELECT 1 FROM delivery_zones WHERE name='Valença Centro');