-- 20260306_003_portal_requests_and_yearly_policy.sql

CREATE TABLE IF NOT EXISTS sales_portal_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(40) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email_verified_at DATETIME DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_sales_portal_email (email),
    KEY idx_sales_portal_email_verified (email, email_verified_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sales_portal_email_tokens (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    token_hash CHAR(64) NOT NULL,
    purpose VARCHAR(40) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_sales_portal_token (token_hash),
    KEY idx_sales_portal_user_purpose (user_id, purpose, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS demo_requests (
    id CHAR(36) PRIMARY KEY,
    portal_user_id BIGINT NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(190) DEFAULT NULL,
    phone VARCHAR(40) DEFAULT NULL,
    requested_plan_type VARCHAR(10) NOT NULL DEFAULT '3',
    requested_pc_devices INT NOT NULL DEFAULT 1,
    requested_web_sessions INT NOT NULL DEFAULT 1,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    admin_message TEXT,
    approved_by VARCHAR(120) DEFAULT NULL,
    rejected_by VARCHAR(120) DEFAULT NULL,
    approved_at DATETIME DEFAULT NULL,
    rejected_at DATETIME DEFAULT NULL,
    approved_customer_id BIGINT DEFAULT NULL,
    demo_code VARCHAR(40) DEFAULT NULL,
    demo_username VARCHAR(120) DEFAULT NULL,
    demo_password_enc TEXT,
    demo_expires_at DATETIME DEFAULT NULL,
    mail_status VARCHAR(20) NOT NULL DEFAULT 'none',
    mail_last_error TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_demo_requests_portal_status (portal_user_id, status, created_at),
    KEY idx_demo_requests_status_created (status, created_at),
    KEY idx_demo_requests_customer (approved_customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS demo_request_messages (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    demo_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_demo_request_created (demo_request_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE payment_requests
    ADD COLUMN IF NOT EXISTS portal_user_id BIGINT DEFAULT NULL AFTER customer_id,
    ADD COLUMN IF NOT EXISTS submitted_amount DECIMAL(19,4) NOT NULL DEFAULT 0 AFTER amount,
    ADD COLUMN IF NOT EXISTS calculated_amount DECIMAL(19,4) NOT NULL DEFAULT 0 AFTER submitted_amount,
    ADD COLUMN IF NOT EXISTS amount_delta DECIMAL(19,4) NOT NULL DEFAULT 0 AFTER calculated_amount,
    ADD COLUMN IF NOT EXISTS amount_mismatch_flag TINYINT(1) NOT NULL DEFAULT 0 AFTER amount_delta;

DELETE FROM pricing_rules
WHERE duration_key NOT IN ('yearly', '2_days');

UPDATE pricing_rules
SET per_web_price = 0
WHERE plan_type = '2';

UPDATE pricing_rules
SET is_active = 1
WHERE duration_key = 'yearly';


