-- KINET MikroTik Admin Module - Update v10 Agen + Profile Voucher Pokok/Jual
-- Jalankan sekali setelah replace file update.

ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS cost_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER price;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS sale_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER cost_price;
ALTER TABLE voucher_profiles ADD COLUMN IF NOT EXISTS agent_fee DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER sale_price;
ALTER TABLE voucher_profiles ADD INDEX IF NOT EXISTS idx_voucher_profiles_price (sale_price, cost_price);

UPDATE voucher_profiles
SET sale_price = CASE WHEN sale_price IS NULL OR sale_price = 0 THEN price ELSE sale_price END,
    cost_price = COALESCE(cost_price, 0),
    agent_fee = GREATEST(CASE WHEN sale_price IS NULL OR sale_price = 0 THEN price ELSE sale_price END - COALESCE(cost_price,0), 0),
    price = CASE WHEN sale_price IS NULL OR sale_price = 0 THEN price ELSE sale_price END;

ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS cost_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER price;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS sale_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER cost_price;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS agent_fee DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER sale_price;
ALTER TABLE vouchers ADD INDEX IF NOT EXISTS idx_vouchers_sold_agent (agent_id, status, created_at);

UPDATE vouchers v
LEFT JOIN voucher_profiles vp ON vp.id = v.voucher_profile_id
SET v.sale_price = CASE WHEN v.sale_price IS NULL OR v.sale_price = 0 THEN COALESCE(vp.sale_price, vp.price, v.price, 0) ELSE v.sale_price END,
    v.cost_price = CASE WHEN v.cost_price IS NULL OR v.cost_price = 0 THEN COALESCE(vp.cost_price, 0) ELSE v.cost_price END,
    v.agent_fee = CASE WHEN v.agent_fee IS NULL OR v.agent_fee = 0 THEN GREATEST(COALESCE(vp.sale_price, vp.price, v.price, 0) - COALESCE(vp.cost_price, 0), 0) ELSE v.agent_fee END,
    v.price = CASE WHEN v.sale_price IS NULL OR v.sale_price = 0 THEN COALESCE(vp.sale_price, vp.price, v.price, 0) ELSE v.sale_price END;

CREATE TABLE IF NOT EXISTS agent_report_resets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    agent_id INT NULL,
    period_type ENUM('weekly','monthly') NOT NULL,
    reset_at DATETIME NOT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_agent_report_resets_agent_period (agent_id, period_type, reset_at),
    CONSTRAINT fk_agent_report_resets_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_agent_report_resets_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;