-- ============================================================
-- LTDA ASSOCIATION MANAGEMENT SYSTEM - DATABASE SCHEMA
-- ============================================================
CREATE DATABASE IF NOT EXISTS ltda_association CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ltda_association;

-- ------------------------------------------------------------
-- USERS (members + admins)
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(30) NOT NULL,
    national_id VARCHAR(50) NOT NULL,
    passport_photo VARCHAR(255) DEFAULT NULL,           -- passport-style photo captured at registration
    passport_face_verified TINYINT(1) NOT NULL DEFAULT 0, -- client-side face-detection result at upload time
    password_hash VARCHAR(255) NOT NULL,
    password_changed_at DATETIME DEFAULT NULL,
    member_type ENUM('driver','investor','both') NOT NULL DEFAULT 'driver',
    role ENUM('member','admin') NOT NULL DEFAULT 'member',
    is_super_admin TINYINT(1) NOT NULL DEFAULT 0,  -- super admins bypass granular permission checks and manage other admins
    status ENUM('pending_otp','active','suspended') NOT NULL DEFAULT 'pending_otp',
    otp_code VARCHAR(10) DEFAULT NULL,
    otp_expires DATETIME DEFAULT NULL,
    email_verified_at DATETIME DEFAULT NULL,
    membership_date DATE DEFAULT NULL,      -- date they became active (used for 6-month loan eligibility)
    member_code VARCHAR(20) DEFAULT NULL UNIQUE,   -- assigned by admin once documents are verified
    documents_verified TINYINT(1) NOT NULL DEFAULT 0,
    documents_verified_at DATETIME DEFAULT NULL,
    documents_verified_by INT DEFAULT NULL,        -- admin user id who verified
    referral_code VARCHAR(20) DEFAULT NULL UNIQUE, -- this member's own code to share with others
    referred_by INT DEFAULT NULL,                  -- user id of the member whose referral code was used at registration
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- MEMBER PROFILE DOCUMENTS
-- ------------------------------------------------------------
CREATE TABLE member_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    national_id_doc VARCHAR(255) DEFAULT NULL,
    drivers_licence_doc VARCHAR(255) DEFAULT NULL,   -- required for drivers
    good_conduct_doc VARCHAR(255) DEFAULT NULL,      -- required for drivers & investors
    residence_doc VARCHAR(255) DEFAULT NULL,
    next_of_kin_name VARCHAR(150) DEFAULT NULL,
    next_of_kin_phone VARCHAR(30) DEFAULT NULL,
    next_of_kin_relationship VARCHAR(100) DEFAULT NULL,
    next_of_kin_doc VARCHAR(255) DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- VEHICLES / TRUCKS (investor fleet management)
-- ------------------------------------------------------------
CREATE TABLE vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,                 -- owner (investor)
    plate_number VARCHAR(30) NOT NULL,
    vehicle_type ENUM('truck','vehicle') NOT NULL DEFAULT 'truck',
    make VARCHAR(100) DEFAULT NULL,
    model VARCHAR(100) DEFAULT NULL,
    year_of_manufacture YEAR DEFAULT NULL,
    capacity VARCHAR(50) DEFAULT NULL,     -- tonnage / seats
    status ENUM('active','maintenance','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SAVINGS (500/month => 300 savings + 200 office operations)
-- ------------------------------------------------------------
CREATE TABLE savings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    month TINYINT NOT NULL,               -- 1-12
    year SMALLINT NOT NULL,
    amount_total DECIMAL(10,2) NOT NULL DEFAULT 500.00,
    savings_portion DECIMAL(10,2) NOT NULL DEFAULT 300.00,
    office_portion DECIMAL(10,2) NOT NULL DEFAULT 200.00,
    recorded_by INT DEFAULT NULL,         -- admin user id
    notes VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_member_month (user_id, month, year),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- REFERRAL BONUSES (KES 100 credited to the referrer's savings once
-- the member they referred completes their profile, i.e. their
-- documents are verified by an admin). One row per completed
-- referral - referred_id is UNIQUE so a member can only ever trigger
-- one bonus, even if verification is later revoked and re-granted.
-- ------------------------------------------------------------
CREATE TABLE referral_bonuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,             -- member who gets the bonus
    referred_id INT NOT NULL UNIQUE,      -- member who completed their profile
    amount DECIMAL(10,2) NOT NULL DEFAULT 100.00,
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- LOANS (50% of accumulated savings, after 6 months membership)
-- ------------------------------------------------------------
CREATE TABLE loans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount_requested DECIMAL(10,2) NOT NULL,
    amount_approved DECIMAL(10,2) DEFAULT NULL,
    status ENUM('pending','approved','rejected','repaid') NOT NULL DEFAULT 'pending',
    purpose VARCHAR(255) DEFAULT NULL,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    decided_by INT DEFAULT NULL,
    decided_at DATETIME DEFAULT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (decided_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ADMIN BROADCAST MESSAGES (meetings / issues, sent via email)
-- ------------------------------------------------------------
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    sent_by INT NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE message_recipients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    user_id INT NOT NULL,
    email_sent TINYINT(1) NOT NULL DEFAULT 0,
    sent_at DATETIME DEFAULT NULL,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- LOADS (cargo/jobs posted per region for drivers/investors)
-- ------------------------------------------------------------
CREATE TABLE loads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(100) NOT NULL,
    cargo_type VARCHAR(150) DEFAULT NULL,
    pickup_point VARCHAR(150) DEFAULT NULL,
    dropoff_point VARCHAR(150) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    contact_phone VARCHAR(30) NOT NULL,   -- who to call about this load
    status ENUM('open','assigned','closed') NOT NULL DEFAULT 'open',
    posted_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (posted_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- who expressed interest / called about a load
CREATE TABLE load_interests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    load_id INT NOT NULL,
    user_id INT NOT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- WELFARE (death / member or relative issue -> contributions & expenses)
-- ------------------------------------------------------------
CREATE TABLE welfare_cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    case_type ENUM('death','medical','other') NOT NULL DEFAULT 'other',
    description TEXT DEFAULT NULL,
    member_id INT DEFAULT NULL,            -- affected member (beneficiary)
    affected_person VARCHAR(150) DEFAULT NULL, -- e.g. "member" or relative's name
    relationship VARCHAR(100) DEFAULT NULL,    -- self / spouse / child / parent etc.
    target_amount DECIMAL(10,2) DEFAULT NULL,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE welfare_contributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT NOT NULL,
    contributor_id INT DEFAULT NULL,   -- NULL = external/anonymous contribution
    contributor_name VARCHAR(150) DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    contributed_at DATE NOT NULL,
    recorded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES welfare_cases(id) ON DELETE CASCADE,
    FOREIGN KEY (contributor_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE welfare_expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    spent_at DATE NOT NULL,
    recorded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES welfare_cases(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ADMIN PERMISSIONS (granular access control for non-super admins.
-- Super admins - users.is_super_admin = 1 - implicitly have every
-- permission and are not represented here.)
-- ------------------------------------------------------------
CREATE TABLE admin_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    permission_key VARCHAR(50) NOT NULL,   -- see includes/permissions.php for the catalog
    granted_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_permission (user_id, permission_key),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SEED: default admin account (password: Admin@123 - CHANGE IMMEDIATELY)
-- Hash below corresponds to "Admin@123"
-- ------------------------------------------------------------
INSERT INTO users (name, email, phone, national_id, password_hash, member_type, role, is_super_admin, status, email_verified_at, membership_date)
VALUES (
  'System Administrator',
  'admin@ltda.local',
  '0700000000',
  '00000000',
  '$2y$10$rP.n/KIcf7acldE.zrb/aOeVdqrXzHi10ieKKA6uaroF0sCfxGy0O',
  'both',
  'admin',
  1,
  'active',
  NOW(),
  CURDATE()
);