-- SaccoFX Database Setup
-- Run this file once to create all tables and seed data
-- Compatible with MySQL 5.7+ / MariaDB 10.3+

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS listings;
DROP TABLE IF EXISTS otp_verifications;
DROP TABLE IF EXISTS buyer_lockouts;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS saccos;
DROP TABLE IF EXISTS admin_users;
DROP TABLE IF EXISTS listing_approvals;
SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────
-- SACCOS
-- ─────────────────────────────────────────────
CREATE TABLE saccos (
    sacco_id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(120) NOT NULL,
    logo_url       VARCHAR(255),
    reg_number     VARCHAR(60) NOT NULL UNIQUE,
    health_score   TINYINT UNSIGNED DEFAULT 75,
    health_label   ENUM('Excellent','Good','Fair','Watch') DEFAULT 'Good',
    status         ENUM('verified','pending') DEFAULT 'pending',
    total_members  INT UNSIGNED DEFAULT 0,
    total_capital  DECIMAL(15,2) DEFAULT 0.00,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- MEMBERS
-- ─────────────────────────────────────────────
CREATE TABLE members (
    member_id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sacco_id        INT UNSIGNED NOT NULL,
    member_number   VARCHAR(30) NOT NULL,
    full_name       VARCHAR(120) NOT NULL,
    phone           VARCHAR(20) NOT NULL,
    email           VARCHAR(120),
    deposit_balance DECIMAL(12,2) DEFAULT 0.00,
    share_balance   DECIMAL(12,2) DEFAULT 0.00,
    share_quantity  INT UNSIGNED DEFAULT 0,
    status          ENUM('active','suspended') DEFAULT 'active',
    kyc_status      ENUM('verified','pending','failed') DEFAULT 'pending',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_member (sacco_id, member_number),
    FOREIGN KEY (sacco_id) REFERENCES saccos(sacco_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- LISTINGS
-- ─────────────────────────────────────────────
CREATE TABLE listings (
    listing_id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sacco_id          INT UNSIGNED NOT NULL,
    seller_member_id  INT UNSIGNED NOT NULL,
    share_quantity    INT UNSIGNED NOT NULL,
    price_per_share   DECIMAL(10,2) NOT NULL,
    total_value       DECIMAL(12,2) GENERATED ALWAYS AS (share_quantity * price_per_share) STORED,
    status            ENUM('available','reserved','sold','cancelled') DEFAULT 'available',
    anonymised_label  VARCHAR(30) NOT NULL,
    approved_by       INT UNSIGNED,
    co_approved_by    INT UNSIGNED,
    created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
    sold_at           DATETIME,
    FOREIGN KEY (sacco_id) REFERENCES saccos(sacco_id),
    FOREIGN KEY (seller_member_id) REFERENCES members(member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- LISTING APPROVALS (dual approval workflow)
-- ─────────────────────────────────────────────
CREATE TABLE listing_approvals (
    approval_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    listing_id   INT UNSIGNED NOT NULL,
    admin_id     INT UNSIGNED NOT NULL,
    role         ENUM('primary','secondary') DEFAULT 'primary',
    approved_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (listing_id) REFERENCES listings(listing_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- RESERVATIONS
-- ─────────────────────────────────────────────
CREATE TABLE reservations (
    reservation_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    listing_id       INT UNSIGNED NOT NULL,
    buyer_member_id  INT UNSIGNED NOT NULL,
    buyer_sacco_id   INT UNSIGNED NOT NULL,
    reserved_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at       DATETIME,
    status           ENUM('pending_payment','paid','expired','cancelled') DEFAULT 'pending_payment',
    payment_method   ENUM('deposit','mpesa','bank') DEFAULT 'deposit',
    queue_position   INT UNSIGNED DEFAULT 1,
    FOREIGN KEY (listing_id) REFERENCES listings(listing_id),
    FOREIGN KEY (buyer_member_id) REFERENCES members(member_id),
    FOREIGN KEY (buyer_sacco_id) REFERENCES saccos(sacco_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- OTP VERIFICATIONS
-- ─────────────────────────────────────────────
CREATE TABLE otp_verifications (
    otp_id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone         VARCHAR(20) NOT NULL,
    member_number VARCHAR(30) NOT NULL,
    sacco_id      INT UNSIGNED NOT NULL,
    otp_code      VARCHAR(6) NOT NULL,
    expires_at    DATETIME NOT NULL,
    used          TINYINT(1) DEFAULT 0,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sacco_id) REFERENCES saccos(sacco_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- BUYER LOCKOUTS (24h lock after expired reservation)
-- ─────────────────────────────────────────────
CREATE TABLE buyer_lockouts (
    lockout_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id       INT UNSIGNED NOT NULL,
    listing_id      INT UNSIGNED NOT NULL,
    locked_until    DATETIME NOT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (listing_id) REFERENCES listings(listing_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- TRANSACTIONS
-- ─────────────────────────────────────────────
CREATE TABLE transactions (
    transaction_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id  INT UNSIGNED NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    method          ENUM('deposit','mpesa','bank') NOT NULL,
    status          ENUM('simulated_success','simulated_fail','pending') DEFAULT 'pending',
    reference       VARCHAR(60),
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- ADMIN USERS
-- ─────────────────────────────────────────────
CREATE TABLE admin_users (
    admin_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sacco_id    INT UNSIGNED,
    username    VARCHAR(60) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    full_name   VARCHAR(120) NOT NULL,
    role        ENUM('superadmin','sacco_admin','officer') DEFAULT 'sacco_admin',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sacco_id) REFERENCES saccos(sacco_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─────────────────────────────────────────────
-- NOTIFICATIONS (simulated)
-- ─────────────────────────────────────────────
CREATE TABLE notifications (
    notif_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id   INT UNSIGNED NOT NULL,
    message     TEXT NOT NULL,
    is_read     TINYINT(1) DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═════════════════════════════════════════════
-- SEED DATA
-- ═════════════════════════════════════════════

-- SACCOs
INSERT INTO saccos (name, logo_url, reg_number, health_score, health_label, status, total_members, total_capital) VALUES
('Fahari Bora SACCO',    'https://ui-avatars.com/api/?name=Fahari+Bora&background=10B981&color=fff&size=128&bold=true',  'CS/2019/004412', 91, 'Excellent', 'verified', 1240, 48500000.00),
('Umoja Wetu SACCO',     'https://ui-avatars.com/api/?name=Umoja+Wetu&background=3B82F6&color=fff&size=128&bold=true',   'CS/2017/002287', 74, 'Good',      'verified',  870, 22100000.00),
('Maendeleo SACCO',      'https://ui-avatars.com/api/?name=Maendeleo&background=F59E0B&color=fff&size=128&bold=true',    'CS/2015/001103', 58, 'Fair',      'verified',  540, 11800000.00),
('Jua Kali SACCO',       'https://ui-avatars.com/api/?name=Jua+Kali&background=EF4444&color=fff&size=128&bold=true',    'CS/2020/005891', 38, 'Watch',     'verified',  310,  5200000.00);

-- MEMBERS (spread across SACCOs)
INSERT INTO members (sacco_id, member_number, full_name, phone, email, deposit_balance, share_balance, share_quantity, status, kyc_status) VALUES
(1, 'FB-1001', 'Amina Wanjiku',      '+254712345678', 'amina@example.com',   85000.00,  240000.00, 600, 'active', 'verified'),
(1, 'FB-1002', 'James Otieno',       '+254723456789', 'james@example.com',  120000.00,  400000.00, 1000,'active', 'verified'),
(1, 'FB-1003', 'Grace Muthoni',      '+254734567890', 'grace@example.com',   42000.00,  160000.00, 400, 'active', 'verified'),
(2, 'UW-2001', 'Peter Kamau',        '+254745678901', 'peter@example.com',   95000.00,  320000.00, 800, 'active', 'verified'),
(2, 'UW-2002', 'Fatuma Hassan',      '+254756789012', 'fatuma@example.com',  30000.00,  120000.00, 300, 'active', 'verified'),
(2, 'UW-2003', 'David Kiprop',       '+254767890123', 'david@example.com',   15000.00,   80000.00, 200, 'active', 'verified'),
(3, 'MA-3001', 'Rose Achieng',       '+254778901234', 'rose@example.com',    60000.00,  200000.00, 500, 'active', 'verified'),
(3, 'MA-3002', 'John Mwangi',        '+254789012345', 'john@example.com',    25000.00,   60000.00, 150, 'active', 'verified'),
(4, 'JK-4001', 'Sarah Njeri',        '+254790123456', 'sarah@example.com',  180000.00,  480000.00, 1200,'active', 'verified'),
(4, 'JK-4002', 'Hassan Abdi',        '+254701234567', 'hassan@example.com',  55000.00,  140000.00, 350, 'active', 'verified');

-- LISTINGS (various states)
INSERT INTO listings (sacco_id, seller_member_id, share_quantity, price_per_share, status, anonymised_label, approved_by, co_approved_by, created_at) VALUES
(1, 1,  500, 420.00, 'available', 'Member #4471', 1, 2, DATE_SUB(NOW(), INTERVAL 2 DAY)),
(1, 2,  800, 400.00, 'available', 'Member #1002', 1, 2, DATE_SUB(NOW(), INTERVAL 1 DAY)),
(2, 4,  600, 280.00, 'reserved',  'Member #7823', 1, 2, DATE_SUB(NOW(), INTERVAL 3 DAY)),
(3, 7,  400, 195.00, 'sold',      'Member #3301', 1, 2, DATE_SUB(NOW(), INTERVAL 7 DAY)),
(4, 9, 1000, 110.00, 'available', 'Member #9901', 1, 2, DATE_SUB(NOW(), INTERVAL 1 DAY));

-- ADMIN USERS (passwords are bcrypt of 'Admin@1234')
INSERT INTO admin_users (sacco_id, username, password, full_name, role) VALUES
(NULL, 'superadmin',   '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMqJqhcNDH5KjD3q.Mk2yJBnRO', 'Super Admin',       'superadmin'),
(1,    'fahari_admin', '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMqJqhcNDH5KjD3q.Mk2yJBnRO', 'Fahari Bora Admin', 'sacco_admin'),
(1,    'fahari_off',   '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMqJqhcNDH5KjD3q.Mk2yJBnRO', 'Fahari Officer',    'officer'),
(2,    'umoja_admin',  '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMqJqhcNDH5KjD3q.Mk2yJBnRO', 'Umoja Wetu Admin',  'sacco_admin'),
(3,    'maendeleo_adm','$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMqJqhcNDH5KjD3q.Mk2yJBnRO', 'Maendeleo Admin',   'sacco_admin'),
(4,    'juakali_adm',  '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMqJqhcNDH5KjD3q.Mk2yJBnRO', 'Jua Kali Admin',    'sacco_admin');

-- RESERVATIONS (listing 3 is reserved by Peter Kamau)
INSERT INTO reservations (listing_id, buyer_member_id, buyer_sacco_id, reserved_at, expires_at, status, payment_method, queue_position)
VALUES (3, 4, 2, DATE_SUB(NOW(), INTERVAL 5 MINUTE), DATE_ADD(NOW(), INTERVAL 10 MINUTE), 'pending_payment', 'mpesa', 1);

-- TRANSACTIONS (sold listing)
INSERT INTO reservations (listing_id, buyer_member_id, buyer_sacco_id, reserved_at, expires_at, status, payment_method, queue_position)
VALUES (4, 5, 2, DATE_SUB(NOW(), INTERVAL 7 DAY), DATE_SUB(NOW(), INTERVAL 7 DAY), 'paid', 'deposit', 1);

INSERT INTO transactions (reservation_id, amount, method, status, reference)
VALUES (2, 78000.00, 'deposit', 'simulated_success', 'TXN-20240521-0042');

UPDATE listings SET sold_at = DATE_SUB(NOW(), INTERVAL 7 DAY) WHERE listing_id = 4;

-- NOTIFICATIONS
INSERT INTO notifications (member_id, message) VALUES
(4, 'Your reservation for Maendeleo SACCO listing (400 shares @ KES 195) is pending payment. You have 15 minutes.'),
(5, 'Congratulations! Your purchase of 400 shares from Maendeleo SACCO is confirmed. Transaction ref: TXN-20240521-0042');

-- Update listing_approvals
INSERT INTO listing_approvals (listing_id, admin_id, role) VALUES
(1, 2, 'primary'), (1, 3, 'secondary'),
(2, 2, 'primary'), (2, 3, 'secondary'),
(3, 4, 'primary'), (3, 2, 'secondary'),
(4, 5, 'primary'), (4, 2, 'secondary'),
(5, 6, 'primary'), (5, 2, 'secondary');