USE acw_sedci_compliance;

-- ============================================================
-- ACW-SEDCI COMPLIANCE AML PORTAL
-- V2.3
-- COMPLIANCE PASSPORT + RISK + EDD + DISCLOSURE
-- ============================================================


-- ============================================================
-- 1. COUNTERPARTY COMPLIANCE PROFILE
-- ============================================================

CREATE TABLE IF NOT EXISTS compliance_profiles (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    counterparty_id BIGINT UNSIGNED NOT NULL UNIQUE,

    compliance_ref VARCHAR(60) NOT NULL UNIQUE,

    counterparty_type ENUM(
        'investor',
        'funder',
        'strategic_partner',
        'government_entity',
        'financial_institution',
        'offtaker',
        'supplier',
        'contractor',
        'intermediary',
        'professional_adviser',
        'other'
    ) NOT NULL,

    onboarding_status ENUM(
        'draft',
        'submitted',
        'under_review',
        'information_required',
        'screening',
        'edd',
        'approved',
        'conditionally_approved',
        'rejected',
        'suspended',
        'expired'
    ) NOT NULL DEFAULT 'draft',

    risk_rating ENUM(
        'low',
        'medium',
        'high',
        'prohibited'
    ) NOT NULL DEFAULT 'medium',

    kyc_status ENUM(
        'pending',
        'incomplete',
        'verified',
        'failed'
    ) NOT NULL DEFAULT 'pending',

    kyb_status ENUM(
        'not_applicable',
        'pending',
        'incomplete',
        'verified',
        'failed'
    ) NOT NULL DEFAULT 'pending',

    beneficial_owner_status ENUM(
        'not_applicable',
        'pending',
        'incomplete',
        'verified',
        'failed'
    ) NOT NULL DEFAULT 'pending',

    source_of_funds_status ENUM(
        'not_required',
        'pending',
        'incomplete',
        'verified',
        'failed'
    ) NOT NULL DEFAULT 'pending',

    sanctions_status ENUM(
        'pending',
        'clear',
        'potential_match',
        'confirmed_match'
    ) NOT NULL DEFAULT 'pending',

    pep_status ENUM(
        'pending',
        'clear',
        'potential_match',
        'confirmed_match'
    ) NOT NULL DEFAULT 'pending',

    adverse_media_status ENUM(
        'pending',
        'clear',
        'review_required',
        'material_concern'
    ) NOT NULL DEFAULT 'pending',

    edd_required TINYINT(1) NOT NULL DEFAULT 0,

    compliance_score DECIMAL(5,2) NULL,

    compliance_decision TEXT NULL,

    approved_by BIGINT UNSIGNED NULL,

    approved_at DATETIME NULL,

    next_review_date DATE NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (counterparty_id)
        REFERENCES counterparties(id)
        ON DELETE CASCADE,

    FOREIGN KEY (approved_by)
        REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_compliance_status(onboarding_status),

    INDEX idx_compliance_risk(risk_rating),

    INDEX idx_compliance_review(next_review_date)
);


-- ============================================================
-- 2. RISK ASSESSMENT
-- ============================================================

CREATE TABLE IF NOT EXISTS compliance_risk_assessments (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    compliance_profile_id BIGINT UNSIGNED NOT NULL,

    jurisdiction_score DECIMAL(5,2) DEFAULT 0,

    ownership_score DECIMAL(5,2) DEFAULT 0,

    transaction_score DECIMAL(5,2) DEFAULT 0,

    source_of_funds_score DECIMAL(5,2) DEFAULT 0,

    sanctions_score DECIMAL(5,2) DEFAULT 0,

    pep_score DECIMAL(5,2) DEFAULT 0,

    adverse_media_score DECIMAL(5,2) DEFAULT 0,

    complexity_score DECIMAL(5,2) DEFAULT 0,

    total_score DECIMAL(7,2) DEFAULT 0,

    risk_rating ENUM(
        'low',
        'medium',
        'high',
        'prohibited'
    ) NOT NULL,

    assessment_notes TEXT,

    assessed_by BIGINT UNSIGNED NULL,

    assessed_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (compliance_profile_id)
        REFERENCES compliance_profiles(id)
        ON DELETE CASCADE,

    FOREIGN KEY (assessed_by)
        REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_risk_profile(compliance_profile_id),

    INDEX idx_risk_rating(risk_rating)
);


-- ============================================================
-- 3. EDD CASES
-- ============================================================

CREATE TABLE IF NOT EXISTS enhanced_due_diligence_cases (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    case_ref VARCHAR(60) NOT NULL UNIQUE,

    compliance_profile_id BIGINT UNSIGNED NOT NULL,

    trigger_reason TEXT NOT NULL,

    status ENUM(
        'open',
        'investigating',
        'information_required',
        'pending_decision',
        'cleared',
        'rejected',
        'closed'
    ) NOT NULL DEFAULT 'open',

    assigned_to BIGINT UNSIGNED NULL,

    opened_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    closed_at DATETIME NULL,

    decision TEXT NULL,

    FOREIGN KEY (compliance_profile_id)
        REFERENCES compliance_profiles(id)
        ON DELETE CASCADE,

    FOREIGN KEY (assigned_to)
        REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_edd_profile(compliance_profile_id),

    INDEX idx_edd_status(status)
);


-- ============================================================
-- 4. SCREENING RECORDS
-- ============================================================

CREATE TABLE IF NOT EXISTS compliance_screenings (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    compliance_profile_id BIGINT UNSIGNED NOT NULL,

    screening_type ENUM(
        'sanctions',
        'pep',
        'adverse_media',
        'watchlist',
        'other'
    ) NOT NULL,

    screening_provider VARCHAR(255),

    search_reference VARCHAR(255),

    result ENUM(
        'clear',
        'potential_match',
        'confirmed_match',
        'review_required'
    ) NOT NULL,

    findings TEXT,

    screened_by BIGINT UNSIGNED NULL,

    screened_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    next_screening_date DATE NULL,

    FOREIGN KEY (compliance_profile_id)
        REFERENCES compliance_profiles(id)
        ON DELETE CASCADE,

    FOREIGN KEY (screened_by)
        REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_screening_profile(compliance_profile_id),

    INDEX idx_screening_type(screening_type)
);


-- ============================================================
-- 5. COMPLIANCE DECISIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS compliance_decisions (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    decision_ref VARCHAR(60) NOT NULL UNIQUE,

    compliance_profile_id BIGINT UNSIGNED NOT NULL,

    decision ENUM(
        'approved',
        'conditionally_approved',
        'rejected',
        'suspended',
        'request_information'
    ) NOT NULL,

    rationale TEXT NOT NULL,

    conditions TEXT NULL,

    decided_by BIGINT UNSIGNED NOT NULL,

    decided_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    review_date DATE NULL,

    FOREIGN KEY (compliance_profile_id)
        REFERENCES compliance_profiles(id)
        ON DELETE CASCADE,

    FOREIGN KEY (decided_by)
        REFERENCES users(id)
        ON DELETE RESTRICT
);


-- ============================================================
-- 6. COMPLIANCE PASSPORTS
-- ============================================================

CREATE TABLE IF NOT EXISTS compliance_passports (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    passport_ref VARCHAR(60) NOT NULL UNIQUE,

    compliance_profile_id BIGINT UNSIGNED NOT NULL UNIQUE,

    passport_status ENUM(
        'draft',
        'active',
        'conditional',
        'suspended',
        'expired',
        'revoked'
    ) NOT NULL DEFAULT 'draft',

    issued_date DATE NULL,

    expiry_date DATE NULL,

    verification_level ENUM(
        'basic',
        'standard',
        'enhanced'
    ) NOT NULL DEFAULT 'standard',

    public_verification_token CHAR(64) NOT NULL UNIQUE,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (compliance_profile_id)
        REFERENCES compliance_profiles(id)
        ON DELETE CASCADE,

    INDEX idx_passport_status(passport_status)
);


-- ============================================================
-- 7. REGULATORY DISCLOSURE REQUESTS
-- ============================================================

CREATE TABLE IF NOT EXISTS regulatory_disclosure_requests (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    request_ref VARCHAR(60) NOT NULL UNIQUE,

    requesting_body VARCHAR(255) NOT NULL,

    requesting_officer VARCHAR(255),

    legal_reference VARCHAR(255),

    request_description TEXT NOT NULL,

    requested_scope TEXT NOT NULL,

    status ENUM(
        'received',
        'under_review',
        'clarification_required',
        'approved',
        'partially_approved',
        'rejected',
        'disclosed',
        'closed'
    ) NOT NULL DEFAULT 'received',

    received_by BIGINT UNSIGNED NULL,

    reviewed_by BIGINT UNSIGNED NULL,

    approved_by BIGINT UNSIGNED NULL,

    received_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    decision_at DATETIME NULL,

    disclosure_at DATETIME NULL,

    decision_notes TEXT,

    FOREIGN KEY (received_by)
        REFERENCES users(id)
        ON DELETE SET NULL,

    FOREIGN KEY (reviewed_by)
        REFERENCES users(id)
        ON DELETE SET NULL,

    FOREIGN KEY (approved_by)
        REFERENCES users(id)
        ON DELETE SET NULL
);


-- ============================================================
-- 8. COMPLIANCE ATTESTATIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS compliance_attestations (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    compliance_profile_id BIGINT UNSIGNED NOT NULL,

    attestation_type VARCHAR(150) NOT NULL,

    declaration_text TEXT NOT NULL,

    accepted TINYINT(1) NOT NULL DEFAULT 0,

    accepted_by BIGINT UNSIGNED NULL,

    accepted_at DATETIME NULL,

    ip_address VARCHAR(45),

    FOREIGN KEY (compliance_profile_id)
        REFERENCES compliance_profiles(id)
        ON DELETE CASCADE,

    FOREIGN KEY (accepted_by)
        REFERENCES users(id)
        ON DELETE SET NULL
);


-- ============================================================
-- 9. SEED ATTESTATION
-- ============================================================

INSERT INTO compliance_attestations
(
    compliance_profile_id,
    attestation_type,
    declaration_text
)
SELECT
    id,
    'responsible_capital',
    'The counterparty confirms that information supplied during onboarding is accurate and complete to the best of its knowledge and that funds and assets proposed for engagement are not knowingly derived from unlawful activity.'
FROM compliance_profiles
WHERE NOT EXISTS (
    SELECT 1
    FROM compliance_attestations ca
    WHERE ca.compliance_profile_id =
          compliance_profiles.id
);


-- ============================================================
-- END V2.3
-- ============================================================