-- ============================================================
--  VECCU - Visionary Elders Co-operative Credit Union
--  Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS veccu CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE veccu;

-- Staff / System Users
CREATE TABLE users (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  staff_no     VARCHAR(20) UNIQUE,
  name         VARCHAR(100) NOT NULL,
  email        VARCHAR(100) UNIQUE,
  phone        VARCHAR(20),
  password     VARCHAR(255) NOT NULL,
  role         ENUM('admin','loan_officer','teller','field_worker') DEFAULT 'teller',
  status       ENUM('active','inactive') DEFAULT 'active',
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default staff (password for all: veccu2024)
-- Hash generated: bcrypt cost=10 for 'veccu2024'
INSERT INTO users (staff_no, name, email, phone, password, role) VALUES
('STAFF-001', 'System Admin', 'admin@veccu.gh', '0546317360',
 '$2y$10$TcgtKQ79ImTGqlcUIcFh9OQg3FBY/xBIQd65EFRITnvUFq75aNKtq', 'admin'),
('STAFF-002', 'Loan Officer', 'loans@veccu.gh', '0206550645',
 '$2y$10$TcgtKQ79ImTGqlcUIcFh9OQg3FBY/xBIQd65EFRITnvUFq75aNKtq', 'loan_officer'),
('STAFF-003', 'Field Worker One', 'field1@veccu.gh', '0240000001',
 '$2y$10$TcgtKQ79ImTGqlcUIcFh9OQg3FBY/xBIQd65EFRITnvUFq75aNKtq', 'field_worker');

-- Members (Clients)
CREATE TABLE members (
  id                        INT AUTO_INCREMENT PRIMARY KEY,
  member_no                 VARCHAR(20) UNIQUE,
  -- Personal Details (matching Loan Application Form)
  surname                   VARCHAR(100) NOT NULL,
  first_name                VARCHAR(100) NOT NULL,
  other_names               VARCHAR(100),
  nationality               VARCHAR(50) DEFAULT 'Ghanaian',
  ghana_card_no             VARCHAR(50),
  marital_status            ENUM('single','married','divorced','widowed'),
  no_of_children            INT DEFAULT 0,
  postal_address            VARCHAR(200),
  home_town                 VARCHAR(100),
  phone_home                VARCHAR(20),
  phone_office              VARCHAR(20),
  phone_mobile              VARCHAR(20) NOT NULL,
  present_address           TEXT,
  length_of_stay            VARCHAR(50),
  accommodation_type        VARCHAR(100),
  previous_address          TEXT,
  educational_qual          VARCHAR(100),
  profession                VARCHAR(100),
  -- Employment Details
  employer_name             VARCHAR(200),
  work_address              TEXT,
  occupation                VARCHAR(100),
  industry                  VARCHAR(100),
  employment_type           ENUM('employed','self-employed','unemployed','retired'),
  -- Referees Details
  contact_person            VARCHAR(100),
  contact_person_rel        VARCHAR(50),
  contact_person_address    TEXT,
  contact_person_phone      VARCHAR(20),
  next_of_kin               VARCHAR(100),
  next_of_kin_rel           VARCHAR(50),
  next_of_kin_address       TEXT,
  next_of_kin_phone         VARCHAR(20),
  -- System
  assigned_field_worker     INT,
  photo                     VARCHAR(255),
  status                    ENUM('active','inactive','suspended') DEFAULT 'active',
  created_by                INT,
  created_at                TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (assigned_field_worker) REFERENCES users(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Accounts (savings, shares, fixed deposit per member)
CREATE TABLE accounts (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  account_no   VARCHAR(20) UNIQUE,
  member_id    INT NOT NULL,
  account_type ENUM('savings','shares','fixed_deposit') DEFAULT 'savings',
  balance      DECIMAL(15,2) DEFAULT 0.00,
  is_primary   TINYINT(1) DEFAULT 0,
  status       ENUM('active','frozen','closed') DEFAULT 'active',
  opened_date  DATE,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (member_id) REFERENCES members(id)
);

-- Passbooks
CREATE TABLE passbooks (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  passbook_no  VARCHAR(20) UNIQUE,
  member_id    INT NOT NULL,
  account_id   INT NOT NULL,
  issued_date  DATE,
  issued_by    INT,
  status       ENUM('active','replaced','lost') DEFAULT 'active',
  FOREIGN KEY (member_id) REFERENCES members(id),
  FOREIGN KEY (account_id) REFERENCES accounts(id),
  FOREIGN KEY (issued_by) REFERENCES users(id)
);

-- Loans
CREATE TABLE loans (
  id                   INT AUTO_INCREMENT PRIMARY KEY,
  loan_no              VARCHAR(20) UNIQUE,
  member_id            INT NOT NULL,
  -- Application Details (matching PDF form)
  amount_requested     DECIMAL(15,2) NOT NULL,
  amount_approved      DECIMAL(15,2),
  amount_disbursed     DECIMAL(15,2),
  purpose              TEXT,
  tenor_months         INT,
  interest_rate        DECIMAL(5,2),
  payment_frequency    ENUM('daily','weekly','bi-weekly','monthly') DEFAULT 'monthly',
  -- Computed Totals
  total_payable        DECIMAL(15,2),
  amount_per_payment   DECIMAL(15,2),
  outstanding_balance  DECIMAL(15,2) DEFAULT 0.00,
  -- Dates
  date_applied         DATE,
  date_approved        DATE,
  date_disbursed       DATE,
  date_first_payment   DATE,
  date_expected_close  DATE,
  -- Status
  status               ENUM('pending','approved','disbursed','active','completed','rejected','defaulted') DEFAULT 'pending',
  rejection_reason     TEXT,
  -- Staff
  created_by           INT,
  approved_by          INT,
  disbursed_by         INT,
  FOREIGN KEY (member_id) REFERENCES members(id),
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (approved_by) REFERENCES users(id)
);

-- Transactions (deposits, withdrawals, loan repayments, disbursements)
CREATE TABLE transactions (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  txn_ref          VARCHAR(30) UNIQUE,
  account_id       INT,
  member_id        INT NOT NULL,
  loan_id          INT,
  type             ENUM('deposit','withdrawal','loan_disbursement','loan_repayment','share_deposit','fee','reversal'),
  amount           DECIMAL(15,2) NOT NULL,
  balance_after    DECIMAL(15,2),
  description      VARCHAR(255),
  channel          ENUM('teller','field_worker','mobile','online') DEFAULT 'teller',
  field_worker_id  INT,
  passbook_no      VARCHAR(20),
  status           ENUM('pending','completed','reversed') DEFAULT 'completed',
  created_by       INT,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES accounts(id),
  FOREIGN KEY (member_id) REFERENCES members(id),
  FOREIGN KEY (loan_id) REFERENCES loans(id),
  FOREIGN KEY (field_worker_id) REFERENCES users(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Field Worker Collections (offline deposits collected from clients)
CREATE TABLE field_collections (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  field_worker_id  INT NOT NULL,
  member_id        INT NOT NULL,
  account_id       INT NOT NULL,
  amount           DECIMAL(15,2) NOT NULL,
  collected_date   DATE,
  passbook_no      VARCHAR(20),
  notes            VARCHAR(255),
  synced           TINYINT(1) DEFAULT 0,
  synced_at        TIMESTAMP NULL,
  txn_id           INT NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (field_worker_id) REFERENCES users(id),
  FOREIGN KEY (member_id) REFERENCES members(id),
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);

-- SMS Log
CREATE TABLE sms_log (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  recipient      VARCHAR(20) NOT NULL,
  member_id      INT,
  message        TEXT NOT NULL,
  status         ENUM('sent','failed','pending') DEFAULT 'pending',
  gateway_ref    VARCHAR(100),
  cost           VARCHAR(20),
  sent_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_by        INT,
  FOREIGN KEY (member_id) REFERENCES members(id),
  FOREIGN KEY (sent_by) REFERENCES users(id)
);

-- Loan Repayment Schedule
CREATE TABLE loan_schedule (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  loan_id          INT NOT NULL,
  installment_no   INT,
  due_date         DATE,
  principal_due    DECIMAL(15,2),
  interest_due     DECIMAL(15,2),
  total_due        DECIMAL(15,2),
  amount_paid      DECIMAL(15,2) DEFAULT 0.00,
  balance          DECIMAL(15,2),
  paid_date        DATE,
  status           ENUM('pending','partial','paid','overdue') DEFAULT 'pending',
  FOREIGN KEY (loan_id) REFERENCES loans(id)
);

-- Audit Log
CREATE TABLE audit_log (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT,
  action      VARCHAR(100),
  table_name  VARCHAR(50),
  record_id   INT,
  details     TEXT,
  ip_address  VARCHAR(45),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);