-- 20260305_002_pricing_payments_demo.sql
CREATE TABLE IF NOT EXISTS pricing_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    plan_type VARCHAR(10) NOT NULL,
    duration_key VARCHAR(40) NOT NULL,
    duration_days INT NOT NULL,
    base_price DECIMAL(19,4) NOT NULL DEFAULT 0,
    per_pc_price DECIMAL(19,4) NOT NULL DEFAULT 0,
    per_web_price DECIMAL(19,4) NOT NULL DEFAULT 0,
    currency VARCHAR(10) NOT NULL DEFAULT 'TRY',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_plan_duration (plan_type, duration_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_accounts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(120) NOT NULL,
    iban VARCHAR(64) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    display_order INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_requests (
    id CHAR(36) PRIMARY KEY,
    payment_code VARCHAR(40) NOT NULL,
    customer_id BIGINT DEFAULT NULL,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(190) DEFAULT NULL,
    phone VARCHAR(40) DEFAULT NULL,
    requested_plan_type VARCHAR(10) NOT NULL,
    requested_pc_devices INT NOT NULL,
    requested_web_sessions INT NOT NULL,
    duration_key VARCHAR(40) NOT NULL,
    amount DECIMAL(19,4) NOT NULL DEFAULT 0,
    currency VARCHAR(10) NOT NULL DEFAULT 'TRY',
    iban_id BIGINT DEFAULT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    admin_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    approved_at DATETIME DEFAULT NULL,
    rejected_at DATETIME DEFAULT NULL,
    reviewed_by VARCHAR(120) DEFAULT NULL,
    UNIQUE KEY uniq_payment_code (payment_code),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_request_messages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    payment_request_id CHAR(36) NOT NULL,
    sender_type VARCHAR(20) NOT NULL,
    sender_name VARCHAR(120) DEFAULT NULL,
    message_text TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payment_created (payment_request_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS demo_licenses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    demo_code VARCHAR(40) NOT NULL,
    starts_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    cancel_reason VARCHAR(120) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_demo_code (demo_code),
    KEY idx_customer_status (customer_id, status),
    KEY idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS backup_files (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    backup_uuid CHAR(36) NOT NULL,
    customer_id BIGINT NOT NULL,
    profile_id CHAR(36) NOT NULL,
    backup_type VARCHAR(20) NOT NULL,
    file_path TEXT NOT NULL,
    checksum_sha256 CHAR(64) NOT NULL,
    file_size BIGINT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    restored_at DATETIME DEFAULT NULL,
    restore_note VARCHAR(255) DEFAULT NULL,
    UNIQUE KEY uniq_backup_uuid (backup_uuid),
    KEY idx_customer_profile_created (customer_id, profile_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pricing_rules (plan_type, duration_key, duration_days, base_price, per_pc_price, per_web_price, currency, is_active)
VALUES
    ('1', 'monthly', 30, 1000, 0, 0, 'TRY', 1),
    ('2', 'monthly', 30, 1500, 250, 0, 'TRY', 1),
    ('3', 'monthly', 30, 2000, 250, 200, 'TRY', 1),
    ('2', 'yearly', 365, 15000, 2500, 0, 'TRY', 1),
    ('3', 'yearly', 365, 20000, 2500, 2000, 'TRY', 1)
ON DUPLICATE KEY UPDATE
    duration_days = VALUES(duration_days),
    base_price = VALUES(base_price),
    per_pc_price = VALUES(per_pc_price),
    per_web_price = VALUES(per_web_price),
    currency = VALUES(currency),
    is_active = VALUES(is_active);


