CREATE TABLE IF NOT EXISTS direct_deposit_setups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    provider_code VARCHAR(50) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    customer_id VARCHAR(120) DEFAULT NULL,
    password_part_one VARCHAR(120) DEFAULT NULL,
    password_part_two VARCHAR(120) DEFAULT NULL,
    bank_account_label VARCHAR(180) DEFAULT NULL,
    bank_account_number VARCHAR(50) DEFAULT NULL,
    live_balance DECIMAL(12,2) DEFAULT NULL,
    registered_mobile VARCHAR(20) DEFAULT NULL,
    secret_pin VARCHAR(20) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    last_synced_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_provider_setup (organization_id, provider_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS zoho_app_integrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    app_code VARCHAR(50) NOT NULL,
    app_name VARCHAR(120) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'inactive',
    connection_name VARCHAR(180) DEFAULT NULL,
    connected_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_app_integration (organization_id, app_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS developer_connections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    service_type VARCHAR(30) NOT NULL DEFAULT 'default',
    service_code VARCHAR(80) NOT NULL,
    service_name VARCHAR(150) NOT NULL,
    connection_name VARCHAR(150) NOT NULL,
    scope_summary TEXT DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_connection_name (organization_id, connection_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incoming_webhooks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    script_body LONGTEXT DEFAULT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS data_backups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    backup_type VARCHAR(30) NOT NULL,
    requested_by VARCHAR(190) DEFAULT NULL,
    recipient_email VARCHAR(190) DEFAULT NULL,
    file_type VARCHAR(20) NOT NULL DEFAULT 'ZIP',
    export_status VARCHAR(30) NOT NULL DEFAULT 'queued',
    download_link VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    attachment_path VARCHAR(255) DEFAULT NULL,
    contact_number VARCHAR(30) DEFAULT NULL,
    criticality VARCHAR(30) NOT NULL DEFAULT 'None',
    created_by VARCHAR(190) DEFAULT NULL,
    created_email VARCHAR(190) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
