-- KINET MikroTik Admin Module - Update v7 Generate Voucher
-- Jalankan sekali setelah replace file update.

CREATE TABLE IF NOT EXISTS voucher_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id INT NULL,
    name VARCHAR(160) NOT NULL,
    mikrotik_profile VARCHAR(160) NOT NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0,
    validity VARCHAR(120) NULL,
    template_color VARCHAR(20) NOT NULL DEFAULT '#2563eb',
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_voucher_profiles_router (router_id),
    INDEX idx_voucher_profiles_status (status),
    CONSTRAINT fk_voucher_profiles_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS voucher_batches (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_comment VARCHAR(180) NOT NULL UNIQUE,
    router_id INT NULL,
    voucher_profile_id INT NULL,
    agent_id INT NULL,
    created_by INT NULL,
    amount_requested INT NOT NULL DEFAULT 0,
    amount_success INT NOT NULL DEFAULT 0,
    amount_failed INT NOT NULL DEFAULT 0,
    status ENUM('processing','success','partial','failed') NOT NULL DEFAULT 'processing',
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_voucher_batches_comment (batch_comment),
    INDEX idx_voucher_batches_agent (agent_id),
    CONSTRAINT fk_voucher_batches_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_voucher_batches_profile FOREIGN KEY (voucher_profile_id) REFERENCES voucher_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_voucher_batches_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_voucher_batches_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vouchers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT NULL,
    router_id INT NULL,
    voucher_profile_id INT NULL,
    agent_id INT NULL,
    created_by INT NULL,
    username VARCHAR(120) NOT NULL,
    password VARCHAR(120) NOT NULL,
    mikrotik_profile VARCHAR(160) NOT NULL,
    batch_comment VARCHAR(180) NOT NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0,
    template_color VARCHAR(20) NOT NULL DEFAULT '#2563eb',
    status ENUM('available','sold','used','expired','cancelled') NOT NULL DEFAULT 'available',
    mikrotik_sync_status ENUM('pending','success','failed','skipped') NOT NULL DEFAULT 'pending',
    mikrotik_sync_message TEXT NULL,
    sold_at DATETIME NULL,
    used_at DATETIME NULL,
    printed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_vouchers_username (username),
    INDEX idx_vouchers_batch_comment (batch_comment),
    INDEX idx_vouchers_status (status),
    INDEX idx_vouchers_profile (voucher_profile_id),
    INDEX idx_vouchers_agent (agent_id),
    CONSTRAINT fk_vouchers_batch FOREIGN KEY (batch_id) REFERENCES voucher_batches(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_profile FOREIGN KEY (voucher_profile_id) REFERENCES voucher_profiles(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_agent FOREIGN KEY (agent_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vouchers_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;