-- =====================================================================
-- ZALO TAXI V6.2 - LỊCH CHUYẾN + GIA HẠN + ĐỐI SOÁT NGÂN HÀNG
-- Nâng từ V6.1. KHÔNG XÓA dữ liệu cũ.
-- Backup database trước khi chạy.
-- =====================================================================
SET @db := DATABASE();
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2) Yêu cầu thanh toán gia hạn
CREATE TABLE IF NOT EXISTS subscription_payment_intents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount BIGINT UNSIGNED NOT NULL,
    payment_content VARCHAR(120) NOT NULL,
    duration_months INT UNSIGNED NOT NULL DEFAULT 1,
    duration_days INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('WAITING','CONFIRMED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'WAITING',
    confirmed_transaction_id VARCHAR(120) NULL,
    expires_at DATETIME NOT NULL,
    confirmed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sub_intent_user (user_id,status,created_at),
    INDEX idx_sub_intent_expiry (status,expires_at),
    CONSTRAINT fk_sub_intent_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) Giao dịch đã dùng. transaction_id UNIQUE để không xác nhận 2 lần.
CREATE TABLE IF NOT EXISTS subscription_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    intent_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    transaction_id VARCHAR(120) NOT NULL UNIQUE,
    amount BIGINT UNSIGNED NOT NULL,
    description TEXT NULL,
    transaction_date DATE NULL,
    raw_json JSON NULL,
    confirmed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sub_tx_user (user_id,confirmed_at),
    INDEX idx_sub_tx_intent (intent_id),
    CONSTRAINT fk_sub_tx_intent FOREIGN KEY (intent_id) REFERENCES subscription_payment_intents(id) ON DELETE RESTRICT,
    CONSTRAINT fk_sub_tx_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4) Nhật ký cộng/trừ thời gian để admin truy vết
CREATE TABLE IF NOT EXISTS subscription_extensions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    source ENUM('PAYMENT','ADMIN') NOT NULL,
    months_added INT NOT NULL DEFAULT 0,
    days_added INT NOT NULL DEFAULT 0,
    old_expires_at DATETIME NULL,
    new_expires_at DATETIME NOT NULL,
    transaction_id VARCHAR(120) NULL,
    admin_id BIGINT UNSIGNED NULL,
    note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sub_ext_user (user_id,created_at),
    CONSTRAINT fk_sub_ext_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_sub_ext_admin FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
