-- ============================================================
-- QUDINA FINTECH APP - COMPLETE DATABASE SCHEMA
-- MySQL 8.0+ with InnoDB, UTF8MB4, proper indexing
-- ============================================================


-- ------------------------------------------------------------
-- 2. USER_PROFILES TABLE (Extended Profile Data)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_profiles (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL UNIQUE,
    avatar_url          VARCHAR(500) NULL,
    date_of_birth       DATE NULL,
    gender              ENUM('male','female','other','prefer_not_say') NULL,
    address             TEXT NULL,
    city                VARCHAR(100) NULL,
    state               VARCHAR(100) NULL,
    occupation          VARCHAR(150) NULL,
    student_status      TINYINT(1) DEFAULT 0,
    institution_name    VARCHAR(255) NULL,
    matric_number       VARCHAR(100) NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_student (student_status, institution_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. KYC_DOCUMENTS TABLE (Identity Documents)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS kyc_documents (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL,
    doc_type            ENUM('nin','bvn','student_id','admission_letter','passport','drivers_license','voters_card') NOT NULL,
    doc_number          VARCHAR(100) NULL,
    file_path           VARCHAR(500) NOT NULL,
    file_original_name  VARCHAR(255) NOT NULL,
    file_size           INT UNSIGNED NULL,
    file_mime_type      VARCHAR(100) NULL,
    verification_status ENUM('pending','verified','rejected') DEFAULT 'pending',
    verified_by         BIGINT UNSIGNED NULL,
    verified_at         DATETIME NULL,
    rejection_reason    TEXT NULL,
    uploaded_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_doc (user_id, doc_type),
    INDEX idx_status (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. SELFIE_CAPTURES TABLE (Biometric Verification)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS selfie_captures (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL UNIQUE,
    file_path           VARCHAR(500) NOT NULL,
    file_original_name  VARCHAR(255) NOT NULL,
    ai_verified         TINYINT(1) DEFAULT 0,
    ai_match_score      DECIMAL(5,2) NULL COMMENT '0.00-100.00 face match confidence',
    ai_verified_at      DATETIME NULL,
    manual_review_status ENUM('pending','approved','rejected') DEFAULT 'pending',
    captured_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. TRANSACTIONS TABLE (All Financial Transactions)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transactions (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_ref     VARCHAR(50) NOT NULL UNIQUE COMMENT 'QDN-TXN-XXXXXXXX',
    user_id             BIGINT UNSIGNED NOT NULL,
    wallet_id           BIGINT UNSIGNED NOT NULL,
    
    -- Transaction Details
    txn_type            ENUM('deposit','withdrawal','transfer_sent','transfer_received','adashi_contribution','adashi_payout','escrow_lock','escrow_release','fee','refund','bonus') NOT NULL,
    status              ENUM('pending','successful','failed','reversed','locked') DEFAULT 'pending',
    amount              DECIMAL(15,2) NOT NULL,
    fee_amount          DECIMAL(15,2) DEFAULT 0.00,
    currency            VARCHAR(3) DEFAULT 'NGN',
    
    -- Parties
    sender_wallet_id    BIGINT UNSIGNED NULL,
    recipient_wallet_id BIGINT UNSIGNED NULL,
    recipient_name      VARCHAR(255) NULL,
    recipient_account   VARCHAR(100) NULL,
    recipient_bank      VARCHAR(100) NULL,
    
    -- Context
    description         TEXT NULL,
    adashi_cycle_id     BIGINT UNSIGNED NULL,
    metadata            JSON NULL COMMENT 'Flexible extra data',
    
    -- Tracking
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at        DATETIME NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
    INDEX idx_user_txn (user_id, created_at),
    INDEX idx_txn_type (txn_type),
    INDEX idx_status (status),
    INDEX idx_txn_ref (transaction_ref),
    INDEX idx_adashi_cycle (adashi_cycle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. ADASHI_CYCLES TABLE (Rotating Savings Groups)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS adashi_cycles (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cycle_tag           VARCHAR(20) NOT NULL UNIQUE COMMENT 'QDN-ADC-XXXXX',
    creator_id          BIGINT UNSIGNED NOT NULL,
    
    -- Cycle Configuration
    name                VARCHAR(255) NOT NULL,
    description         TEXT NULL,
    icon_type           ENUM('users','graduation-cap','heart-pulse','briefcase','custom') DEFAULT 'users',
    icon_color          VARCHAR(20) DEFAULT 'blue',
    
    -- Financial
    contribution_amount DECIMAL(15,2) NOT NULL COMMENT 'Amount per member per period',
    frequency           ENUM('daily','weekly','biweekly','monthly') NOT NULL,
    max_members         INT UNSIGNED NOT NULL DEFAULT 10,
    current_members     INT UNSIGNED DEFAULT 1,
    total_pool_value    DECIMAL(15,2) GENERATED ALWAYS AS (contribution_amount * max_members) STORED,
    
    -- Status
    status              ENUM('open','active','paused','completed','cancelled') DEFAULT 'open',
    visibility          ENUM('public','private','invite_only') DEFAULT 'public',
    
    -- Trust & Entry
    min_trust_score     INT DEFAULT 0 COMMENT 'Minimum trust score to join',
    entry_fee           DECIMAL(15,2) DEFAULT 0.00,
    
    -- Timing
    start_date          DATE NULL,
    end_date            DATE NULL,
    next_contribution_date DATE NULL,
    next_payout_date    DATE NULL,
    
    -- Payout Order (JSON array of member positions)
    payout_order        JSON NULL,
    current_payout_position INT DEFAULT 1,
    
    -- Escrow
    escrow_enabled      TINYINT(1) DEFAULT 1,
    total_escrowed      DECIMAL(15,2) DEFAULT 0.00,
    
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_visibility (visibility),
    INDEX idx_creator (creator_id),
    INDEX idx_frequency (frequency),
    INDEX idx_trust_score (min_trust_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. CYCLE_MEMBERS TABLE (Members in Adashi Cycles)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cycle_members (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cycle_id            BIGINT UNSIGNED NOT NULL,
    user_id             BIGINT UNSIGNED NOT NULL,
    
    -- Member Status
    join_status         ENUM('invited','joined','active','completed','left','removed') DEFAULT 'joined',
    payout_position     INT UNSIGNED NULL COMMENT 'Position in payout rotation',
    has_received_payout TINYINT(1) DEFAULT 0,
    
    -- Tracking
    total_contributed   DECIMAL(15,2) DEFAULT 0.00,
    total_received      DECIMAL(15,2) DEFAULT 0.00,
    last_contribution_at DATETIME NULL,
    joined_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (cycle_id) REFERENCES adashi_cycles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_cycle_member (cycle_id, user_id),
    INDEX idx_cycle_user (cycle_id, user_id),
    INDEX idx_join_status (join_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. CONTRIBUTIONS TABLE (Individual Contributions)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contributions (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cycle_id            BIGINT UNSIGNED NOT NULL,
    member_id           BIGINT UNSIGNED NOT NULL,
    user_id             BIGINT UNSIGNED NOT NULL,
    transaction_id      BIGINT UNSIGNED NULL,
    
    -- Contribution Details
    amount              DECIMAL(15,2) NOT NULL,
    period_number       INT UNSIGNED NOT NULL COMMENT 'Which period this contribution is for',
    status              ENUM('pending','paid','late','missed','refunded') DEFAULT 'pending',
    paid_at             DATETIME NULL,
    
    -- Escrow
    escrow_released     TINYINT(1) DEFAULT 0,
    escrow_released_at  DATETIME NULL,
    
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (cycle_id) REFERENCES adashi_cycles(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES cycle_members(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    INDEX idx_cycle_period (cycle_id, period_number),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. NOTIFICATIONS TABLE (User Alerts & Updates)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL,
    
    -- Notification Content
    title               VARCHAR(255) NOT NULL,
    message             TEXT NOT NULL,
    notification_type   ENUM('transaction','adashi','kyc','system','security','promo') NOT NULL,
    priority            ENUM('low','normal','high','urgent') DEFAULT 'normal',
    
    -- Action & Navigation
    action_url          VARCHAR(500) NULL,
    related_entity_type VARCHAR(50) NULL COMMENT 'cycle, transaction, etc.',
    related_entity_id   BIGINT UNSIGNED NULL,
    
    -- Status
    is_read             TINYINT(1) DEFAULT 0,
    read_at             DATETIME NULL,
    
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_read (user_id, is_read),
    INDEX idx_type (notification_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. AUDIT_LOG TABLE (Security & Compliance Tracking)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NULL,
    action              VARCHAR(100) NOT NULL COMMENT 'login, transfer, withdrawal, etc.',
    entity_type         VARCHAR(50) NULL,
    entity_id           BIGINT UNSIGNED NULL,
    ip_address          VARCHAR(45) NULL,
    user_agent          VARCHAR(500) NULL,
    details             JSON NULL,
    risk_level          ENUM('low','medium','high','critical') DEFAULT 'low',
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at),
    INDEX idx_risk (risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Insert sample Adashi cycles
INSERT INTO adashi_cycles (cycle_tag, creator_id, name, description, icon_type, icon_color, contribution_amount, frequency, max_members, current_members, status, visibility, min_trust_score, entry_fee, start_date, next_contribution_date, next_payout_date, current_payout_position, payout_order) VALUES
('QDN-ADC-001', 1, 'NYSC Group', 'Monthly contributions for NYSC corps members', 'users', 'blue', 120000.00, 'monthly', 10, 10, 'active', 'private', 50, 1000.00, '2024-01-15', '2024-05-20', '2024-05-30', 4, '[1,2,3,4,5,6,7,8,9,10]'),
('QDN-ADC-002', 2, 'Level 400 Set', 'Weekly savings for final year students', 'graduation-cap', 'indigo', 100000.00, 'weekly', 8, 8, 'active', 'private', 60, 500.00, '2024-03-01', '2024-05-19', '2024-05-26', 2, '[2,4,6,8,1,3,5,7]'),
('QDN-ADC-003', 3, 'Weekend Fam', 'Casual weekend family savings', 'heart-pulse', 'orange', 50000.00, 'weekly', 6, 6, 'active', 'private', 40, 500.00, '2024-04-01', '2024-05-18', NULL, 5, '[1,3,5,2,4,6]');

-- Insert cycle members
INSERT INTO cycle_members (cycle_id, user_id, join_status, payout_position, has_received_payout, total_contributed) VALUES
-- NYSC Group
(1, 1, 'active', 4, 0, 480000.00),
(1, 2, 'active', 1, 1, 480000.00),
(1, 3, 'active', 3, 0, 480000.00),
(1, 4, 'active', 5, 0, 360000.00),
(1, 5, 'active', 2, 1, 360000.00),
-- Level 400 Set
(2, 2, 'active', 2, 0, 400000.00),
(2, 1, 'active', 4, 0, 400000.00),
(2, 3, 'active', 6, 0, 300000.00),
-- Weekend Fam
(3, 3, 'active', 5, 0, 200000.00),
(3, 1, 'active', 1, 1, 250000.00);

-- Insert sample transactions
INSERT INTO transactions (transaction_ref, user_id, wallet_id, txn_type, status, amount, currency, description, created_at, completed_at) VALUES
('QDN-TXN-000001', 1, 1, 'deposit', 'successful', 50000.00, 'NGN', 'Bank Transfer', '2024-05-18 08:45:00', '2024-05-18 08:45:00'),
('QDN-TXN-000002', 1, 1, 'adashi_contribution', 'locked', 10000.00, 'NGN', 'Adashi Contribution - NYSC Group', '2024-05-18 07:20:00', '2024-05-18 07:20:00'),
('QDN-TXN-000003', 1, 1, 'escrow_lock', 'locked', 44000.00, 'NGN', 'Escrow Lock - Level 400 Set', '2024-05-17 18:30:00', '2024-05-17 18:30:00'),
('QDN-TXN-000004', 1, 1, 'withdrawal', 'successful', 20000.00, 'NGN', 'Withdrawal to Bank', '2024-05-16 11:00:00', '2024-05-16 11:00:00'),
('QDN-TXN-000005', 1, 1, 'transfer_sent', 'successful', 5000.00, 'NGN', 'Transfer to Daniel V.', '2024-05-15 21:15:00', '2024-05-15 21:15:00'),
('QDN-TXN-000006', 1, 1, 'adashi_payout', 'successful', 30000.00, 'NGN', 'Adashi Payout - Weekend Fam', '2024-05-14 18:40:00', '2024-05-14 18:40:00');

-- Insert notifications
INSERT INTO notifications (user_id, title, message, notification_type, priority, related_entity_type, related_entity_id) VALUES
(1, 'Contribution due in NYSC Group', 'Your monthly contribution of ₦120,000 is due today at 8:00 AM', 'adashi', 'urgent', 'cycle', 1),
(1, 'Level 400 Set payout in 2 days', 'You will receive your payout of ₦800,000 on May 19 at 6:30 PM', 'adashi', 'high', 'cycle', 2),
(1, 'New guarantor request', 'Chinonso Okafor has requested you to be their guarantor', 'system', 'normal', NULL, NULL);

-- Insert KYC documents (demo)
INSERT INTO kyc_documents (user_id, doc_type, doc_number, file_path, file_original_name, verification_status, uploaded_at) VALUES
(1, 'nin', '12345678901', '/uploads/kyc/nin_1_1705000000.pdf', 'nin_document.pdf', 'verified', '2024-01-10 10:00:00'),
(1, 'bvn', '22145678901', '/uploads/kyc/bvn_1_1705000001.pdf', 'bvn_document.pdf', 'verified', '2024-01-10 10:05:00'),
(2, 'nin', '22345678902', '/uploads/kyc/nin_2_1705000002.pdf', 'nin_document.pdf', 'verified', '2024-01-12 09:00:00'),
(3, 'nin', '32345678903', '/uploads/kyc/nin_3_1705000003.pdf', 'nin_document.pdf', 'pending', '2024-02-01 14:00:00');
