CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO roles (id,name) VALUES (1,'super_admin'),(2,'founder_admin'),(3,'board_admin'),(4,'finance_officer'),(5,'loan_officer'),(6,'member');

CREATE TABLE shareholders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  shareholder_code VARCHAR(50) NOT NULL UNIQUE,
  shareholder_type ENUM('founder','investor','new_founder','institutional') NOT NULL,
  legal_name VARCHAR(180) NOT NULL,
  email VARCHAR(150) NULL,
  phone VARCHAR(50) NULL,
  kyc_status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  admission_date DATE NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE share_rounds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  open_date DATE NULL,
  close_date DATE NULL,
  nominal_value DECIMAL(18,2) NOT NULL DEFAULT 1000,
  subscription_price DECIMAL(18,2) NOT NULL DEFAULT 5000,
  max_shares INT NULL,
  status ENUM('draft','open','closed') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO share_rounds (name, open_date, close_date, nominal_value, subscription_price, max_shares, status) VALUES
('Founders Round','2023-01-01','2023-12-31',1000,1000,30000,'closed'),
('June 2026 Public Round','2026-06-01','2026-08-31',1000,5000,20000,'draft');

CREATE TABLE share_subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shareholder_id INT NOT NULL,
  round_id INT NOT NULL,
  amount_paid DECIMAL(18,2) NOT NULL,
  shares_allocated INT NOT NULL,
  nominal_amount DECIMAL(18,2) NOT NULL,
  premium_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  payment_status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  approval_status ENUM('submitted','approved','allotted','rejected') NOT NULL DEFAULT 'submitted',
  allotted_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id),
  FOREIGN KEY (round_id) REFERENCES share_rounds(id)
);

CREATE TABLE share_transfers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  from_shareholder_id INT NOT NULL,
  to_shareholder_id INT NOT NULL,
  shares_transferred INT NOT NULL,
  transfer_price DECIMAL(18,2) NULL,
  request_status ENUM('submitted','approved','settled','rejected') NOT NULL DEFAULT 'submitted',
  approved_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_shareholder_id) REFERENCES shareholders(id),
  FOREIGN KEY (to_shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE ownership_snapshots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shareholder_id INT NOT NULL,
  snapshot_date DATE NOT NULL,
  shares_held INT NOT NULL,
  ownership_percent DECIMAL(18,6) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE dividend_declarations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  profit_amount DECIMAL(18,2) NOT NULL,
  reserve_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  dividend_pool DECIMAL(18,2) NOT NULL,
  dividend_per_share DECIMAL(18,6) NOT NULL,
  status ENUM('draft','approved','paid') NOT NULL DEFAULT 'draft',
  approved_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE dividend_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  declaration_id INT NOT NULL,
  shareholder_id INT NOT NULL,
  eligible_shares INT NOT NULL,
  gross_amount DECIMAL(18,2) NOT NULL,
  paid_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  payment_date DATE NULL,
  payment_status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (declaration_id) REFERENCES dividend_declarations(id),
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE loan_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  interest_rate DECIMAL(8,4) NOT NULL,
  term_months INT NOT NULL,
  method ENUM('flat','reducing') NOT NULL DEFAULT 'flat',
  penalty_rate DECIMAL(8,4) NOT NULL DEFAULT 0,
  status TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE loans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shareholder_id INT NOT NULL,
  loan_product_id INT NULL,
  principal_amount DECIMAL(18,2) NOT NULL,
  interest_rate DECIMAL(8,4) NOT NULL,
  disbursement_date DATE NOT NULL,
  maturity_date DATE NOT NULL,
  status ENUM('pending','active','arrears','closed','rejected') NOT NULL DEFAULT 'pending',
  outstanding_principal DECIMAL(18,2) NOT NULL DEFAULT 0,
  accrued_interest DECIMAL(18,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id),
  FOREIGN KEY (loan_product_id) REFERENCES loan_products(id)
);

CREATE TABLE loan_schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  loan_id INT NOT NULL,
  installment_no INT NOT NULL,
  due_date DATE NOT NULL,
  principal_due DECIMAL(18,2) NOT NULL,
  interest_due DECIMAL(18,2) NOT NULL,
  amount_due DECIMAL(18,2) NOT NULL,
  amount_paid DECIMAL(18,2) NOT NULL DEFAULT 0,
  status ENUM('pending','part_paid','paid','overdue') NOT NULL DEFAULT 'pending',
  FOREIGN KEY (loan_id) REFERENCES loans(id)
);

CREATE TABLE repayments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  loan_id INT NOT NULL,
  payment_date DATE NOT NULL,
  amount_paid DECIMAL(18,2) NOT NULL,
  applied_to_principal DECIMAL(18,2) NOT NULL DEFAULT 0,
  applied_to_interest DECIMAL(18,2) NOT NULL DEFAULT 0,
  applied_to_penalty DECIMAL(18,2) NOT NULL DEFAULT 0,
  gateway_name VARCHAR(50) NULL,
  external_reference VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (loan_id) REFERENCES loans(id)
);

CREATE TABLE expense_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL
);
INSERT INTO expense_categories (name) VALUES ('Salaries'),('Utilities'),('Hosting'),('Legal'),('Transport'),('Marketing');

CREATE TABLE expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  expense_date DATE NOT NULL,
  description TEXT NULL,
  approval_status ENUM('draft','approved','paid') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES expense_categories(id)
);

CREATE TABLE income_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(150) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  income_date DATE NOT NULL,
  description TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE chart_of_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_code VARCHAR(20) NOT NULL UNIQUE,
  account_name VARCHAR(180) NOT NULL,
  account_type ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  parent_id INT NULL,
  active TINYINT NOT NULL DEFAULT 1,
  FOREIGN KEY (parent_id) REFERENCES chart_of_accounts(id)
);
INSERT INTO chart_of_accounts (account_code, account_name, account_type, parent_id) VALUES
('101','Share Capital','equity',NULL),
('104','Share Premium','equity',NULL),
('121','Retained Earnings','equity',NULL),
('411','Loans Receivable','asset',NULL),
('512','Bank','asset',NULL),
('571','Mobile Money','asset',NULL),
('601','Salaries Expense','expense',NULL),
('602','Utilities Expense','expense',NULL),
('603','Hosting Expense','expense',NULL),
('701','Service Revenue','revenue',NULL),
('751','Interest Income','revenue',NULL),
('761','Referral Reward Expense','expense',NULL);

CREATE TABLE journal_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  journal_no VARCHAR(50) NOT NULL UNIQUE,
  entry_date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  source_module VARCHAR(50) NULL,
  status ENUM('draft','posted') NOT NULL DEFAULT 'posted',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE journal_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  journal_entry_id INT NOT NULL,
  account_id INT NOT NULL,
  debit DECIMAL(18,2) NOT NULL DEFAULT 0,
  credit DECIMAL(18,2) NOT NULL DEFAULT 0,
  shareholder_id INT NULL,
  loan_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id),
  FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id),
  FOREIGN KEY (loan_id) REFERENCES loans(id)
);

CREATE TABLE payment_webhooks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  gateway_name VARCHAR(50) NOT NULL,
  event_id VARCHAR(150) NOT NULL,
  event_type VARCHAR(150) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  processed TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY unique_gateway_event (gateway_name, event_id)
);

CREATE TABLE payment_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shareholder_id INT NULL,
  purpose ENUM('share_subscription','recurring_contribution','loan_repayment','fee','reward','dividend') NOT NULL,
  gateway_name VARCHAR(50) NOT NULL,
  provider_reference VARCHAR(150) NULL,
  internal_reference VARCHAR(150) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'XAF',
  status ENUM('pending','paid','failed','reversed') NOT NULL DEFAULT 'pending',
  metadata_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE recurring_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shareholder_id INT NOT NULL,
  gateway_name VARCHAR(50) NOT NULL,
  external_plan_id VARCHAR(150) NULL,
  purpose ENUM('share_subscription','contribution','loan_repayment') NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  interval_name ENUM('weekly','monthly','quarterly','yearly') NOT NULL,
  status ENUM('active','paused','cancelled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE referral_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shareholder_id INT NOT NULL,
  code VARCHAR(50) NOT NULL UNIQUE,
  status TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE referral_rewards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referrer_shareholder_id INT NOT NULL,
  referred_shareholder_id INT NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  reward_amount DECIMAL(18,2) NOT NULL,
  approval_status ENUM('pending','approved','paid','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_shareholder_id) REFERENCES shareholders(id),
  FOREIGN KEY (referred_shareholder_id) REFERENCES shareholders(id)
);

CREATE TABLE board_resolutions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  resolution_type VARCHAR(100) NOT NULL,
  decision_date DATE NOT NULL,
  status ENUM('draft','approved','rejected') NOT NULL DEFAULT 'draft',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(150) NOT NULL,
  module VARCHAR(100) NOT NULL,
  reference_id VARCHAR(100) NULL,
  details TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE OR REPLACE VIEW v_share_positions AS
SELECT shareholder_id,
       SUM(share_delta) AS current_shares
FROM (
  SELECT shareholder_id, shares_allocated AS share_delta
  FROM share_subscriptions
  WHERE approval_status = 'allotted'
  UNION ALL
  SELECT from_shareholder_id AS shareholder_id, -shares_transferred AS share_delta
  FROM share_transfers WHERE request_status IN ('approved','settled')
  UNION ALL
  SELECT to_shareholder_id AS shareholder_id, shares_transferred AS share_delta
  FROM share_transfers WHERE request_status IN ('approved','settled')
) x
GROUP BY shareholder_id;
