SET NAMES utf8mb4;
SET time_zone = '+07:00';

CREATE TABLE pesantren (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  address TEXT NULL,
  phone VARCHAR(30) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pesantren_id BIGINT UNSIGNED NOT NULL,
  role ENUM('super_admin','operator','guardian','canteen_owner','canteen_cashier','auditor') NOT NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(190) NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_phone_tenant (pesantren_id, phone),
  CONSTRAINT fk_user_pesantren FOREIGN KEY (pesantren_id) REFERENCES pesantren(id)
) ENGINE=InnoDB;

CREATE TABLE students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pesantren_id BIGINT UNSIGNED NOT NULL,
  nis VARCHAR(50) NOT NULL,
  name VARCHAR(120) NOT NULL,
  class_name VARCHAR(80) NULL,
  dormitory VARCHAR(80) NULL,
  status ENUM('active','inactive','graduated') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_nis (pesantren_id, nis),
  CONSTRAINT fk_student_pesantren FOREIGN KEY (pesantren_id) REFERENCES pesantren(id)
) ENGINE=InnoDB;

CREATE TABLE guardian_students (
  guardian_user_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  relationship VARCHAR(40) NOT NULL DEFAULT 'Orang tua',
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (guardian_user_id, student_id),
  CONSTRAINT fk_gs_guardian FOREIGN KEY (guardian_user_id) REFERENCES users(id),
  CONSTRAINT fk_gs_student FOREIGN KEY (student_id) REFERENCES students(id)
) ENGINE=InnoDB;

CREATE TABLE student_cards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  card_number VARCHAR(40) NOT NULL UNIQUE,
  qr_token_hash CHAR(64) NOT NULL UNIQUE,
  pin_hash VARCHAR(255) NOT NULL,
  pin_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  status ENUM('active','blocked','lost','expired') NOT NULL DEFAULT 'active',
  valid_until DATE NULL,
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_card_student FOREIGN KEY (student_id) REFERENCES students(id)
) ENGINE=InnoDB;

CREATE TABLE wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL UNIQUE,
  balance BIGINT NOT NULL DEFAULT 0,
  held_balance BIGINT NOT NULL DEFAULT 0,
  version INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_wallet_student FOREIGN KEY (student_id) REFERENCES students(id),
  CHECK (balance >= 0),
  CHECK (held_balance >= 0)
) ENGINE=InnoDB;

CREATE TABLE spending_limits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  period ENUM('daily','weekly','monthly') NOT NULL,
  amount BIGINT NOT NULL,
  category VARCHAR(80) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  set_by BIGINT UNSIGNED NOT NULL,
  effective_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_limit (student_id, period, category),
  CONSTRAINT fk_limit_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_limit_setter FOREIGN KEY (set_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE canteens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pesantren_id BIGINT UNSIGNED NOT NULL,
  owner_user_id BIGINT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  ownership ENUM('internal','external') NOT NULL,
  settlement_bank VARCHAR(60) NULL,
  settlement_account VARCHAR(80) NULL,
  settlement_name VARCHAR(120) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_canteen_pesantren FOREIGN KEY (pesantren_id) REFERENCES pesantren(id),
  CONSTRAINT fk_canteen_owner FOREIGN KEY (owner_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  canteen_id BIGINT UNSIGNED NOT NULL,
  sku VARCHAR(60) NULL,
  name VARCHAR(140) NOT NULL,
  category VARCHAR(80) NULL,
  price BIGINT NOT NULL,
  stock INT NULL,
  image_path VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_product_sku (canteen_id, sku),
  CONSTRAINT fk_product_canteen FOREIGN KEY (canteen_id) REFERENCES canteens(id)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(50) NOT NULL UNIQUE,
  canteen_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  cashier_user_id BIGINT UNSIGNED NOT NULL,
  total BIGINT NOT NULL,
  status ENUM('pending_pin','paid','cancelled','refunded') NOT NULL DEFAULT 'pending_pin',
  paid_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_orders_student_date (student_id, created_at),
  INDEX idx_orders_canteen_date (canteen_id, created_at),
  CONSTRAINT fk_order_canteen FOREIGN KEY (canteen_id) REFERENCES canteens(id),
  CONSTRAINT fk_order_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_order_cashier FOREIGN KEY (cashier_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  product_name VARCHAR(140) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price BIGINT NOT NULL,
  subtotal BIGINT NOT NULL,
  CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE payment_intents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(60) NOT NULL UNIQUE,
  wallet_id BIGINT UNSIGNED NOT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  gateway VARCHAR(30) NOT NULL,
  external_id VARCHAR(120) NULL,
  amount BIGINT NOT NULL,
  fee BIGINT NOT NULL DEFAULT 0,
  status ENUM('created','pending','paid','failed','expired','refunded') NOT NULL DEFAULT 'created',
  payment_url TEXT NULL,
  expires_at DATETIME NULL,
  paid_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pi_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id),
  CONSTRAINT fk_pi_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE manual_topups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(60) NOT NULL UNIQUE,
  wallet_id BIGINT UNSIGNED NOT NULL,
  operator_user_id BIGINT UNSIGNED NOT NULL,
  amount BIGINT NOT NULL,
  cash_received BIGINT NOT NULL,
  note VARCHAR(255) NULL,
  status ENUM('posted','void') NOT NULL DEFAULT 'posted',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_mt_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id),
  CONSTRAINT fk_mt_operator FOREIGN KEY (operator_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE wallet_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wallet_id BIGINT UNSIGNED NOT NULL,
  reference_type ENUM('topup_gateway','topup_manual','purchase','refund','adjustment','settlement_hold') NOT NULL,
  reference_id BIGINT UNSIGNED NOT NULL,
  direction ENUM('credit','debit') NOT NULL,
  amount BIGINT NOT NULL,
  balance_before BIGINT NOT NULL,
  balance_after BIGINT NOT NULL,
  description VARCHAR(255) NOT NULL,
  idempotency_key VARCHAR(100) NOT NULL UNIQUE,
  actor_user_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ledger_wallet_date (wallet_id, created_at),
  CONSTRAINT fk_ledger_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id),
  CONSTRAINT fk_ledger_actor FOREIGN KEY (actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE gateway_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gateway VARCHAR(30) NOT NULL,
  event_key VARCHAR(190) NOT NULL,
  signature_valid TINYINT(1) NOT NULL,
  payload JSON NOT NULL,
  processing_status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
  error_message TEXT NULL,
  received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  UNIQUE KEY uq_gateway_event (gateway, event_key)
) ENGINE=InnoDB;

CREATE TABLE settlements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(60) NOT NULL UNIQUE,
  canteen_id BIGINT UNSIGNED NOT NULL,
  period_start DATETIME NOT NULL,
  period_end DATETIME NOT NULL,
  gross_amount BIGINT NOT NULL,
  fee_amount BIGINT NOT NULL DEFAULT 0,
  net_amount BIGINT NOT NULL,
  status ENUM('draft','submitted','approved','paid','rejected') NOT NULL DEFAULT 'draft',
  submitted_at DATETIME NULL,
  approved_by BIGINT UNSIGNED NULL,
  paid_at DATETIME NULL,
  proof_path VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_settlement_canteen FOREIGN KEY (canteen_id) REFERENCES canteens(id),
  CONSTRAINT fk_settlement_approver FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE bank_reconciliations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reconciliation_date DATE NOT NULL,
  bank_balance BIGINT NOT NULL,
  ledger_balance BIGINT NOT NULL,
  difference_amount BIGINT NOT NULL,
  status ENUM('matched','difference','resolved') NOT NULL,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reconciliation_date (reconciliation_date),
  CONSTRAINT fk_recon_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE whatsapp_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  phone VARCHAR(30) NOT NULL,
  event_type VARCHAR(60) NOT NULL,
  message TEXT NOT NULL,
  provider_message_id VARCHAR(120) NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  response JSON NULL,
  sent_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_wa_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  old_values JSON NULL,
  new_values JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_entity (entity_type, entity_id),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;