-- Track My Money + Seed & Bread + Net Worth + Reconciliation (MySQL 8+)
SET NAMES utf8mb4;
SET time_zone = '+03:00';

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  name VARCHAR(190) NOT NULL,
  role ENUM('admin','manager','viewer') NOT NULL DEFAULT 'viewer',
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(190) NOT NULL UNIQUE,
  setting_value LONGTEXT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tracked_identifiers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('paybill','till','msisdn') NOT NULL,
  value VARCHAR(64) NOT NULL,
  label VARCHAR(190) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  UNIQUE KEY uq_tracked (type,value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS provider_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(64) NOT NULL,
  request LONGTEXT NULL,
  response LONGTEXT NULL,
  http_code INT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_provider_created (provider, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mpesa_incoming_transactions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  receipt VARCHAR(64) NOT NULL UNIQUE,
  amount DECIMAL(12,2) NOT NULL,
  msisdn VARCHAR(32) NULL,
  payer_name VARCHAR(190) NULL,
  account_ref VARCHAR(190) NULL,
  identifier_id INT NULL,
  raw_payload JSON NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'success',
  txn_time DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_incoming_time (txn_time),
  INDEX idx_incoming_identifier (identifier_id),
  INDEX idx_incoming_msisdn (msisdn),
  CONSTRAINT fk_incoming_identifier FOREIGN KEY (identifier_id) REFERENCES tracked_identifiers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mpesa_outgoing_transactions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  destination_type ENUM('phone','paybill','till') NOT NULL DEFAULT 'phone',
  destination_value VARCHAR(64) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  narration VARCHAR(190) NULL,
  identifier_id INT NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'initiated',
  txn_time DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_outgoing_time (txn_time),
  INDEX idx_outgoing_dest (destination_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(32) NOT NULL,
  name VARCHAR(190) NOT NULL,
  identifier_value VARCHAR(64) NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'KES',
  opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_reconciled_at DATETIME NULL,
  last_reconciled_balance DECIMAL(12,2) NULL,
  created_at DATETIME NULL,
  INDEX idx_accounts_type (type),
  INDEX idx_accounts_identifier (identifier_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incomes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  category_id INT NULL,
  amount DECIMAL(12,2) NOT NULL,
  received_at DATETIME NOT NULL,
  reference VARCHAR(190) NULL,
  notes TEXT NULL,
  mpesa_incoming_id BIGINT NULL,
  INDEX idx_income_account (account_id),
  INDEX idx_income_date (received_at),
  CONSTRAINT fk_income_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expense_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  parent_id INT NULL,
  UNIQUE KEY uq_cat (name, parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expense_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL UNIQUE,
  category_id INT NULL,
  unit VARCHAR(32) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  INDEX idx_item_cat (category_id),
  CONSTRAINT fk_item_cat FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expenses (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  category_id INT NULL,
  item_id INT NULL,
  amount DECIMAL(12,2) NOT NULL,
  quantity DECIMAL(12,2) NULL,
  unit_cost DECIMAL(12,2) NULL,
  merchant VARCHAR(190) NULL,
  spent_at DATETIME NOT NULL,
  reference VARCHAR(190) NULL,
  notes TEXT NULL,
  mpesa_outgoing_id BIGINT NULL,
  INDEX idx_exp_account (account_id),
  INDEX idx_exp_date (spent_at),
  INDEX idx_exp_item (item_id),
  CONSTRAINT fk_exp_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_exp_item FOREIGN KEY (item_id) REFERENCES expense_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS projected_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  category_id INT NULL,
  item_id INT NULL,
  planned_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  priority VARCHAR(16) NOT NULL DEFAULT 'must',
  recurrence VARCHAR(16) NOT NULL DEFAULT 'monthly',
  active TINYINT(1) NOT NULL DEFAULT 1,
  INDEX idx_projected_month (month_key),
  INDEX idx_projected_cat (category_id),
  CONSTRAINT fk_projected_item FOREIGN KEY (item_id) REFERENCES expense_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS projected_snapshots (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  total_planned DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_snap_month (month_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bucket_allocations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  income_id BIGINT NOT NULL,
  projected_item_id BIGINT NOT NULL,
  allocated_amount DECIMAL(12,2) NOT NULL,
  pct_used DECIMAL(9,6) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_alloc_month (month_key),
  INDEX idx_alloc_income (income_id),
  CONSTRAINT fk_alloc_income FOREIGN KEY (income_id) REFERENCES incomes(id) ON DELETE CASCADE,
  CONSTRAINT fk_alloc_projected FOREIGN KEY (projected_item_id) REFERENCES projected_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bucket_balances (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  projected_item_id BIGINT NOT NULL,
  balance_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_balance (month_key, projected_item_id),
  INDEX idx_balance_month (month_key),
  CONSTRAINT fk_balance_projected FOREIGN KEY (projected_item_id) REFERENCES projected_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bucket_transfers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  from_projected_item_id BIGINT NOT NULL,
  to_projected_item_id BIGINT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  transferred_at DATETIME NOT NULL,
  notes TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transfers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  from_account_id INT NOT NULL,
  to_account_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  transferred_at DATETIME NOT NULL,
  reference VARCHAR(190) NULL,
  notes TEXT NULL,
  INDEX idx_tr_from (from_account_id),
  INDEX idx_tr_to (to_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loans (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('borrowed','lent') NOT NULL,
  counterparty VARCHAR(190) NOT NULL,
  principal DECIMAL(12,2) NOT NULL,
  interest_type VARCHAR(32) NOT NULL DEFAULT 'none',
  interest_rate DECIMAL(9,4) NOT NULL DEFAULT 0,
  start_date DATE NULL,
  due_date DATE NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_payments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  loan_id BIGINT NOT NULL,
  account_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  paid_at DATETIME NOT NULL,
  allocation_json JSON NULL,
  notes TEXT NULL,
  CONSTRAINT fk_loan_pay FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS savings_goals (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  target_amount DECIMAL(12,2) NOT NULL,
  interest_type VARCHAR(32) NOT NULL DEFAULT 'none',
  interest_rate DECIMAL(9,4) NOT NULL DEFAULT 0,
  start_date DATE NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS savings_contributions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  savings_goal_id BIGINT NOT NULL,
  account_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  contributed_at DATETIME NOT NULL,
  notes TEXT NULL,
  CONSTRAINT fk_save_goal FOREIGN KEY (savings_goal_id) REFERENCES savings_goals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assets (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  type VARCHAR(64) NOT NULL,
  purchase_date DATE NULL,
  purchase_cost DECIMAL(12,2) NULL,
  current_value DECIMAL(12,2) NOT NULL DEFAULT 0,
  depreciation_type VARCHAR(32) NOT NULL DEFAULT 'none',
  depreciation_rate DECIMAL(9,4) NOT NULL DEFAULT 0,
  ownership_pct DECIMAL(6,2) NOT NULL DEFAULT 100,
  location VARCHAR(190) NULL,
  notes TEXT NULL,
  linked_account_id INT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  INDEX idx_asset_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS asset_valuations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  asset_id BIGINT NOT NULL,
  valuation_date DATE NOT NULL,
  value DECIMAL(12,2) NOT NULL,
  notes TEXT NULL,
  INDEX idx_asset_val (asset_id, valuation_date),
  CONSTRAINT fk_asset_val FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS liabilities (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  type VARCHAR(64) NOT NULL,
  original_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  outstanding_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  interest_type VARCHAR(32) NOT NULL DEFAULT 'none',
  interest_rate DECIMAL(9,4) NOT NULL DEFAULT 0,
  start_date DATE NULL,
  due_date DATE NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'active',
  linked_loan_id BIGINT NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS liability_updates (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  liability_id BIGINT NOT NULL,
  update_date DATE NOT NULL,
  outstanding_amount DECIMAL(12,2) NOT NULL,
  notes TEXT NULL,
  INDEX idx_liab_upd (liability_id, update_date),
  CONSTRAINT fk_liab_upd FOREIGN KEY (liability_id) REFERENCES liabilities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reconciliations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  reconciled_at DATETIME NOT NULL,
  system_balance DECIMAL(12,2) NOT NULL,
  actual_balance DECIMAL(12,2) NOT NULL,
  variance DECIMAL(12,2) NOT NULL,
  reason VARCHAR(64) NOT NULL,
  notes TEXT NULL,
  reference VARCHAR(190) NULL,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_rec_account (account_id, reconciled_at),
  CONSTRAINT fk_rec_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reconciliation_adjustments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  reconciliation_id BIGINT NOT NULL,
  income_id BIGINT NULL,
  expense_id BIGINT NULL,
  adjustment_type ENUM('credit','debit') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_adj_rec (reconciliation_id),
  CONSTRAINT fk_adj_rec FOREIGN KEY (reconciliation_id) REFERENCES reconciliations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
