-- Co-operative Society Management SaaS — Database Schema (MySQL 5.7+/MariaDB 10.3+)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ================= PLATFORM (SaaS owner) =================
CREATE TABLE IF NOT EXISTS platform_settings (
  k VARCHAR(100) PRIMARY KEY,
  v TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS super_admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  last_login DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  max_members INT NOT NULL DEFAULT 0,
  max_staff INT NOT NULL DEFAULT 0,
  max_branches INT NOT NULL DEFAULT 1,
  modules VARCHAR(500) NOT NULL DEFAULT '',
  description TEXT,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  name_local VARCHAR(200) NULL,
  tagline VARCHAR(255) NULL,
  reg_no VARCHAR(100) NULL,
  reg_date DATE NULL,
  est_year SMALLINT NULL,
  address TEXT NULL,
  district VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  pin VARCHAR(10) NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(150) NULL,
  office_hours VARCHAR(150) NULL,
  logo VARCHAR(255) NULL,
  primary_color VARCHAR(10) DEFAULT '#14463b',
  secondary_color VARCHAR(10) DEFAULT '#b7862b',
  lang VARCHAR(5) DEFAULT 'gu',
  plan_id INT NULL,
  status ENUM('trial','active','suspended','closed') NOT NULL DEFAULT 'trial',
  trial_ends DATE NULL,
  sub_ends DATE NULL,
  modules VARCHAR(500) NOT NULL DEFAULT '',
  settings MEDIUMTEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenant_domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  domain VARCHAR(190) NOT NULL UNIQUE,
  type ENUM('custom','subdomain') NOT NULL DEFAULT 'custom',
  verify_status ENUM('pending','verified','failed') NOT NULL DEFAULT 'pending',
  ssl_status ENUM('pending','active','error') NOT NULL DEFAULT 'pending',
  is_primary TINYINT NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_checked DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  plan_id INT NULL,
  invoice_no VARCHAR(50) NOT NULL UNIQUE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  base_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  addons_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','paid','cancelled') NOT NULL DEFAULT 'unpaid',
  paid_on DATE NULL,
  payment_ref VARCHAR(150) NULL,
  notes TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NULL,
  category VARCHAR(50) NOT NULL,
  subject VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
  status ENUM('open','assigned','in_progress','waiting','resolved','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ticket_replies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  by_platform TINYINT NOT NULL DEFAULT 0,
  author VARCHAR(150) NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS announcements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  target ENUM('all','trial','active','plan') NOT NULL DEFAULT 'all',
  plan_id INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(64) NOT NULL,
  login VARCHAR(150) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ================= TENANT (Society) =================
CREATE TABLE IF NOT EXISTS branches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(150) NOT NULL,
  address TEXT NULL,
  phone VARCHAR(30) NULL,
  is_head TINYINT NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE (tenant_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  branch_id INT NULL,
  member_id INT NULL,
  name VARCHAR(150) NOT NULL,
  login VARCHAR(150) NOT NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  designation VARCHAR(100) NULL,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  joined_on DATE NULL,
  last_login DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (tenant_id, login),
  INDEX (tenant_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  branch_id INT NULL,
  member_no VARCHAR(30) NULL,
  application_no VARCHAR(30) NULL,
  member_type VARCHAR(30) NOT NULL DEFAULT 'regular',
  name VARCHAR(150) NOT NULL,
  father_name VARCHAR(150) NULL,
  gender ENUM('male','female','other') NULL,
  dob DATE NULL,
  mobile VARCHAR(20) NOT NULL,
  alt_mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  address TEXT NULL,
  village VARCHAR(100) NULL,
  city VARCHAR(100) NULL,
  taluka VARCHAR(100) NULL,
  district VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  pin VARCHAR(10) NULL,
  occupation VARCHAR(100) NULL,
  pan VARCHAR(10) NULL,
  id_type VARCHAR(30) NULL,
  id_number VARCHAR(50) NULL,
  nominee_name VARCHAR(150) NULL,
  nominee_relation VARCHAR(50) NULL,
  nominee_mobile VARCHAR(20) NULL,
  photo_doc_id INT NULL,
  sign_doc_id INT NULL,
  kyc_status ENUM('pending','submitted','under_verification','verified','rejected','expired') NOT NULL DEFAULT 'pending',
  status ENUM('pending','active','inactive','suspended','resigned','transferred','closed','rejected') NOT NULL DEFAULT 'pending',
  joined_on DATE NULL,
  exit_on DATE NULL,
  remarks TEXT NULL,
  created_by INT NULL,
  approved_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (tenant_id, member_no),
  INDEX (tenant_id, mobile),
  INDEX (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  member_id INT NULL,
  entity_type VARCHAR(30) NOT NULL,
  entity_id INT NULL,
  doc_type VARCHAR(50) NOT NULL,
  title VARCHAR(200) NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NOT NULL,
  size INT NOT NULL,
  is_public TINYINT NOT NULL DEFAULT 0,
  status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  remarks VARCHAR(255) NULL,
  uploaded_by INT NULL,
  verified_by INT NULL,
  verified_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, entity_type, entity_id),
  INDEX (tenant_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kyc_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  member_id INT NOT NULL,
  from_status VARCHAR(30) NULL,
  to_status VARCHAR(30) NOT NULL,
  remarks VARCHAR(255) NULL,
  user_id INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Chart of accounts
CREATE TABLE IF NOT EXISTS ledger_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(150) NOT NULL,
  type ENUM('asset','liability','equity','income','expense') NOT NULL,
  sys_key VARCHAR(30) NULL,
  is_system TINYINT NOT NULL DEFAULT 0,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE (tenant_id, code),
  INDEX (tenant_id, sys_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deposit_schemes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  product ENUM('FD','RD') NOT NULL,
  name VARCHAR(150) NOT NULL,
  rate DECIMAL(6,2) NOT NULL,
  min_months INT NOT NULL DEFAULT 12,
  max_months INT NOT NULL DEFAULT 60,
  min_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  description TEXT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  INDEX (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS interest_rates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  product VARCHAR(10) NOT NULL,
  rate DECIMAL(6,2) NOT NULL,
  effective_from DATE NOT NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, product, effective_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Member accounts: SB savings, CS compulsory savings, FD, RD, SH share
CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  branch_id INT NULL,
  member_id INT NOT NULL,
  product VARCHAR(5) NOT NULL,
  scheme_id INT NULL,
  account_no VARCHAR(30) NOT NULL,
  opened_on DATE NOT NULL,
  status ENUM('active','matured','closed') NOT NULL DEFAULT 'active',
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  rate DECIMAL(6,2) NULL,
  principal DECIMAL(14,2) NULL,
  installment DECIMAL(14,2) NULL,
  tenure_months INT NULL,
  maturity_date DATE NULL,
  maturity_amount DECIMAL(14,2) NULL,
  last_interest_date DATE NULL,
  closed_on DATE NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (tenant_id, account_no),
  INDEX (tenant_id, member_id),
  INDEX (tenant_id, product, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS account_txns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  account_id INT NOT NULL,
  voucher_id INT NOT NULL,
  txn_date DATE NOT NULL,
  particulars VARCHAR(255) NULL,
  kind VARCHAR(20) NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (account_id, txn_date, id),
  INDEX (tenant_id, voucher_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  rate DECIMAL(6,2) NOT NULL,
  method ENUM('reducing','flat') NOT NULL DEFAULT 'reducing',
  max_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  max_tenure INT NOT NULL DEFAULT 60,
  penalty_rate DECIMAL(6,2) NOT NULL DEFAULT 2,
  guarantors_required TINYINT NOT NULL DEFAULT 1,
  description TEXT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  INDEX (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  branch_id INT NULL,
  member_id INT NOT NULL,
  loan_type_id INT NOT NULL,
  application_no VARCHAR(30) NOT NULL,
  loan_no VARCHAR(30) NULL,
  applied_amount DECIMAL(14,2) NOT NULL,
  tenure_months INT NOT NULL,
  purpose VARCHAR(255) NULL,
  monthly_income DECIMAL(14,2) NULL,
  guarantor1_id INT NULL,
  guarantor2_id INT NULL,
  rate DECIMAL(6,2) NOT NULL,
  method ENUM('reducing','flat') NOT NULL,
  sanction_amount DECIMAL(14,2) NULL,
  emi DECIMAL(14,2) NULL,
  status ENUM('applied','under_review','approved','rejected','active','closed','written_off') NOT NULL DEFAULT 'applied',
  applied_on DATE NOT NULL,
  reviewed_by INT NULL,
  approved_by INT NULL,
  approved_on DATE NULL,
  disbursed_on DATE NULL,
  principal_outstanding DECIMAL(14,2) NOT NULL DEFAULT 0,
  closed_on DATE NULL,
  remarks TEXT NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (tenant_id, application_no),
  INDEX (tenant_id, member_id),
  INDEX (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_installments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  loan_id INT NOT NULL,
  inst_no INT NOT NULL,
  due_date DATE NOT NULL,
  principal DECIMAL(14,2) NOT NULL,
  interest DECIMAL(14,2) NOT NULL,
  emi DECIMAL(14,2) NOT NULL,
  paid_principal DECIMAL(14,2) NOT NULL DEFAULT 0,
  paid_interest DECIMAL(14,2) NOT NULL DEFAULT 0,
  paid_on DATE NULL,
  status ENUM('pending','partial','paid','waived') NOT NULL DEFAULT 'pending',
  INDEX (loan_id, inst_no),
  INDEX (tenant_id, due_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recovery_visits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  loan_id INT NOT NULL,
  visit_date DATE NOT NULL,
  remarks TEXT NULL,
  promised_amount DECIMAL(14,2) NULL,
  next_followup DATE NULL,
  user_id INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, loan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vouchers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  branch_id INT NULL,
  voucher_no VARCHAR(40) NOT NULL,
  type ENUM('RV','PV','JV','TV') NOT NULL,
  voucher_date DATE NOT NULL,
  fy VARCHAR(9) NOT NULL,
  mode ENUM('cash','cheque','bank','transfer') NOT NULL DEFAULT 'cash',
  member_id INT NULL,
  amount DECIMAL(14,2) NOT NULL,
  narration VARCHAR(255) NULL,
  ref_no VARCHAR(100) NULL,
  purpose VARCHAR(50) NULL,
  status ENUM('pending','posted','rejected','reversed') NOT NULL DEFAULT 'pending',
  reversal_of INT NULL,
  reversed_by INT NULL,
  created_by INT NULL,
  approved_by INT NULL,
  posted_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (tenant_id, voucher_no),
  INDEX (tenant_id, voucher_date),
  INDEX (tenant_id, member_id),
  INDEX (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS voucher_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  voucher_id INT NOT NULL,
  ledger_account_id INT NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  account_id INT NULL,
  loan_id INT NULL,
  installment_id INT NULL,
  kind VARCHAR(20) NULL,
  narration VARCHAR(255) NULL,
  INDEX (voucher_id),
  INDEX (tenant_id, ledger_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cheques (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  voucher_id INT NOT NULL,
  member_id INT NULL,
  direction ENUM('received','issued') NOT NULL DEFAULT 'received',
  cheque_no VARCHAR(30) NOT NULL,
  bank_name VARCHAR(150) NULL,
  cheque_date DATE NULL,
  amount DECIMAL(14,2) NOT NULL,
  status ENUM('received','deposited','cleared','bounced','returned','cancelled') NOT NULL DEFAULT 'received',
  deposited_on DATE NULL,
  cleared_on DATE NULL,
  remarks VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS number_series (
  tenant_id INT NOT NULL,
  series VARCHAR(30) NOT NULL,
  next_no INT NOT NULL DEFAULT 1,
  PRIMARY KEY (tenant_id, series)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  member_id INT NOT NULL,
  category VARCHAR(50) NOT NULL,
  description TEXT NOT NULL,
  status ENUM('submitted','under_review','in_progress','approved','rejected','completed') NOT NULL DEFAULT 'submitted',
  assigned_to INT NULL,
  resolution TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  category VARCHAR(30) NOT NULL DEFAULT 'general',
  title VARCHAR(200) NOT NULL,
  body TEXT NULL,
  doc_id INT NULL,
  is_public TINYINT NOT NULL DEFAULT 1,
  publish_on DATE NOT NULL,
  expires_on DATE NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'published',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, publish_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS management (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  designation VARCHAR(100) NOT NULL,
  phone VARCHAR(30) NULL,
  show_phone TINYINT NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  INDEX (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NULL,
  member_id INT NULL,
  title VARCHAR(200) NOT NULL,
  body TEXT NULL,
  is_read TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, member_id),
  INDEX (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NULL,
  user_id INT NULL,
  actor VARCHAR(150) NULL,
  role VARCHAR(30) NULL,
  action VARCHAR(50) NOT NULL,
  module VARCHAR(50) NOT NULL,
  record_id VARCHAR(50) NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  ip VARCHAR(64) NULL,
  device VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, created_at),
  INDEX (tenant_id, module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;