CREATE DATABASE IF NOT EXISTS kyc_portal;
USE kyc_portal;

CREATE TABLE kyc_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id VARCHAR(16) UNIQUE,
    company_id INT,
    data JSON,
    business_reg VARCHAR(255),
    contact_id VARCHAR(255),
    proof_address VARCHAR(255),
    status VARCHAR(50) DEFAULT 'Pending',
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (company_id) REFERENCES companies(id)
);

CREATE TABLE kyc_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT,
    name VARCHAR(100),
    label VARCHAR(255),
    type ENUM('text', 'textarea', 'file', 'checkbox', 'number', 'email', 'tel', 'url'),
    required TINYINT(1) DEFAULT 0,
    section ENUM('business', 'contact', 'compliance', 'documents'),
    order_index INT,
    active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id)
);

CREATE TABLE integrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    api_key VARCHAR(255),
    api_secret VARCHAR(255),
    updated_at DATETIME
);

CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    action VARCHAR(255),
    submission_id VARCHAR(16),
    created_at DATETIME,
    FOREIGN KEY (admin_id) REFERENCES admins(id)
);

INSERT INTO kyc_fields (company_id, name, label, type, required, section, order_index, active) VALUES
(1, 'business_name', 'Business Name', 'text', 1, 'business', 1, 1),
(1, 'business_reg', 'Business Registration Document', 'file', 1, 'documents', 2, 1),
(1, 'contact_email', 'Contact Email', 'email', 1, 'contact', 3, 1),
(1, 'contact_id', 'Contact ID Document', 'file', 1, 'documents', 4, 1),
(1, 'proof_address', 'Proof of Address', 'file', 1, 'documents', 5, 1),
(1, 'privacy_consent', 'I agree to the privacy policy', 'checkbox', 1, 'compliance', 6, 1);