-- ShuleTrack MVP schema (single-school, SaaS-ready)
-- Import this into MySQL/MariaDB

CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'SchoolAdmin',
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_email (email),
  KEY idx_users_tenant (tenant_id),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(190) NOT NULL,
  county VARCHAR(120) NULL,
  subcounty VARCHAR(120) NULL,
  category VARCHAR(60) NULL,
  school_type VARCHAR(60) NULL,
  students INT NOT NULL DEFAULT 0,
  streams INT NOT NULL DEFAULT 0,
  contacts TEXT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_school_tenant (tenant_id),
  CONSTRAINT fk_schools_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fiscal_years (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  label VARCHAR(60) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_closed TINYINT(1) NOT NULL DEFAULT 0,
  KEY idx_fy_tenant (tenant_id, start_date),
  CONSTRAINT fk_fy_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  bank VARCHAR(120) NULL,
  branch VARCHAR(120) NULL,
  account_no VARCHAR(80) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'Active',
  balance DECIMAL(16,2) NOT NULL DEFAULT 0,
  KEY idx_bank_tenant (tenant_id),
  CONSTRAINT fk_bank_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_drawer (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  as_at_date DATE NOT NULL,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_cash_date (tenant_id, as_at_date),
  CONSTRAINT fk_cash_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vote_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  grp VARCHAR(40) NOT NULL,
  name VARCHAR(190) NOT NULL,
  note_no VARCHAR(20) NULL,
  stmt_line VARCHAR(190) NULL,
  KEY idx_vote_tenant (tenant_id, grp),
  CONSTRAINT fk_vote_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  fiscal_year_id INT NOT NULL,
  date DATE NOT NULL,
  reference VARCHAR(255) NULL,
  mode VARCHAR(20) NOT NULL DEFAULT 'Bank',
  bank_account_id INT NULL,
  bank_label VARCHAR(120) NULL,
  total_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  created_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_receipts_tenant (tenant_id, fiscal_year_id, date),
  CONSTRAINT fk_receipts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_receipts_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receipt_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  receipt_id INT NOT NULL,
  vote_item_id INT NOT NULL,
  description VARCHAR(255) NULL,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  KEY idx_rl_receipt (receipt_id),
  CONSTRAINT fk_rl_receipt FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE CASCADE,
  CONSTRAINT fk_rl_vote FOREIGN KEY (vote_item_id) REFERENCES vote_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  fiscal_year_id INT NOT NULL,
  date DATE NOT NULL,
  supplier VARCHAR(190) NOT NULL,
  reference VARCHAR(255) NULL,
  mode VARCHAR(20) NOT NULL DEFAULT 'Bank',
  bank_account_id INT NULL,
  bank_label VARCHAR(120) NULL,
  total_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  created_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_payments_tenant (tenant_id, fiscal_year_id, date),
  CONSTRAINT fk_payments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_payments_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payment_id INT NOT NULL,
  vote_item_id INT NOT NULL,
  description VARCHAR(255) NULL,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  KEY idx_pl_payment (payment_id),
  CONSTRAINT fk_pl_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_pl_vote FOREIGN KEY (vote_item_id) REFERENCES vote_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payables (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  supplier VARCHAR(190) NOT NULL,
  first_date DATE NOT NULL,
  last_date DATE NOT NULL,
  balance DECIMAL(16,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_payable_supplier (tenant_id, supplier),
  CONSTRAINT fk_payables_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receivables (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  debtor VARCHAR(190) NOT NULL,
  first_date DATE NOT NULL,
  last_date DATE NOT NULL,
  balance DECIMAL(16,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_recv_debtor (tenant_id, debtor),
  CONSTRAINT fk_receivables_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS budget_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  fiscal_year_id INT NOT NULL,
  vote_item_id INT NOT NULL,
  original_budget DECIMAL(16,2) NOT NULL DEFAULT 0,
  adjustments DECIMAL(16,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_budget_line (tenant_id, fiscal_year_id, vote_item_id),
  CONSTRAINT fk_budget_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_budget_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id),
  CONSTRAINT fk_budget_vote FOREIGN KEY (vote_item_id) REFERENCES vote_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NULL,
  action VARCHAR(40) NOT NULL,
  entity VARCHAR(60) NOT NULL,
  entity_id INT NOT NULL DEFAULT 0,
  details_json TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_tenant (tenant_id, created_at),
  CONSTRAINT fk_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default tenant
INSERT INTO tenants (id, name, is_active) VALUES (1, 'Default School Tenant', 1)
  ON DUPLICATE KEY UPDATE name=VALUES(name);

-- NOTE: Create the admin user via /public/install.php (it hashes password correctly).

-- Governance
CREATE TABLE IF NOT EXISTS bom_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(190) NOT NULL,
  position VARCHAR(120) NULL,
  phone VARCHAR(60) NULL,
  term_from DATE NULL,
  term_to DATE NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bom_tenant (tenant_id),
  CONSTRAINT fk_bom_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS committee_meetings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  committee VARCHAR(120) NOT NULL,
  meeting_date DATE NOT NULL,
  agenda VARCHAR(255) NULL,
  minutes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cm_tenant (tenant_id, meeting_date),
  CONSTRAINT fk_cm_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Projects
CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  funding_source VARCHAR(120) NULL,
  start_date DATE NULL,
  status VARCHAR(60) NULL,
  budget DECIMAL(16,2) NOT NULL DEFAULT 0,
  spent DECIMAL(16,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_projects_tenant (tenant_id),
  CONSTRAINT fk_projects_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assets
CREATE TABLE IF NOT EXISTS assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  asset_name VARCHAR(255) NOT NULL,
  asset_class VARCHAR(120) NOT NULL,
  acq_date DATE NULL,
  cost DECIMAL(16,2) NOT NULL DEFAULT 0,
  asset_condition VARCHAR(60) NULL,
  location VARCHAR(120) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_assets_tenant (tenant_id, asset_class),
  CONSTRAINT fk_assets_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
