-- HamBaa KAYA - Initial Schema v1
-- Usage: mysql -u USER -p DB < sql/001_init.sql

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

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role ENUM('guest','host','admin') NOT NULL DEFAULT 'guest',
  wa_id VARCHAR(64) NOT NULL UNIQUE,
  phone VARCHAR(32) NULL,
  full_name VARCHAR(160) NULL,
  status ENUM('active','blocked','pending') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kyc_profiles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL UNIQUE,
  tim_status ENUM('none','pending','passed','failed') NOT NULL DEFAULT 'none',
  doc_refs JSON NULL,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT fk_kyc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS listings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  host_id INT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  location_text VARCHAR(255) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  base_price_night_minor INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'ZAR',
  status ENUM('draft','active','paused','archived') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_listings_host (host_id),
  CONSTRAINT fk_listings_host FOREIGN KEY (host_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS listing_availability (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  listing_id INT UNSIGNED NOT NULL,
  day DATE NOT NULL,
  is_available TINYINT(1) NOT NULL DEFAULT 1,
  price_override_minor INT NULL,
  min_nights INT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_listing_day (listing_id, day),
  KEY idx_avail_day (day),
  CONSTRAINT fk_avail_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bookings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  listing_id INT UNSIGNED NOT NULL,
  guest_id INT UNSIGNED NOT NULL,
  host_id INT UNSIGNED NOT NULL,
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  nights INT NOT NULL DEFAULT 1,
  status ENUM(
    'DRAFT','PENDING_PAYMENT','PAID_ESCROW','CONFIRMED','CHECKED_IN','COMPLETED',
    'CANCELLED_GUEST','CANCELLED_HOST','REFUND_PENDING','REFUNDED','DISPUTED','VOID'
  ) NOT NULL DEFAULT 'DRAFT',
  price_subtotal_minor INT NOT NULL DEFAULT 0,
  fees_total_minor INT NOT NULL DEFAULT 0,
  grand_total_minor INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'ZAR',
  cancellation_policy_code VARCHAR(50) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bookings_guest (guest_id),
  KEY idx_bookings_host (host_id),
  KEY idx_bookings_listing (listing_id),
  CONSTRAINT fk_bookings_listing FOREIGN KEY (listing_id) REFERENCES listings(id),
  CONSTRAINT fk_bookings_guest FOREIGN KEY (guest_id) REFERENCES users(id),
  CONSTRAINT fk_bookings_host FOREIGN KEY (host_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_intents (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  provider ENUM('peach') NOT NULL DEFAULT 'peach',
  merchant_txn_id VARCHAR(80) NOT NULL,
  amount_minor INT NOT NULL,
  currency CHAR(3) NOT NULL,
  status ENUM('created','pending','succeeded','failed','refunded','disputed') NOT NULL DEFAULT 'created',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_merchant_txn (merchant_txn_id),
  KEY idx_intents_booking (booking_id),
  CONSTRAINT fk_intents_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider VARCHAR(32) NOT NULL,
  event_id VARCHAR(100) NOT NULL,
  merchant_txn_id VARCHAR(80) NULL,
  payload_json JSON NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  process_status ENUM('pending','ok','error') NOT NULL DEFAULT 'pending',
  error_message VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_event (provider, event_id),
  KEY idx_wh_merchant (merchant_txn_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wallets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL UNIQUE,
  currency CHAR(3) NOT NULL DEFAULT 'ZAR',
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS escrow_accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL UNIQUE,
  status ENUM('holding','released','refunded','frozen') NOT NULL DEFAULT 'holding',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT fk_escrow_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ledger_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scope_type ENUM('wallet','escrow','platform') NOT NULL,
  scope_id INT UNSIGNED NOT NULL,
  booking_id INT UNSIGNED NULL,
  ref_type VARCHAR(32) NOT NULL,
  ref_id VARCHAR(120) NOT NULL,
  direction ENUM('debit','credit') NOT NULL,
  amount_minor INT NOT NULL,
  currency CHAR(3) NOT NULL,
  meta_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_idem (scope_type, scope_id, ref_type, ref_id, direction),
  KEY idx_ledger_booking (booking_id),
  KEY idx_ledger_scope (scope_type, scope_id),
  CONSTRAINT fk_ledger_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS withdrawals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  amount_minor INT NOT NULL,
  fee_minor INT NOT NULL,
  payout_minor INT NOT NULL,
  currency CHAR(3) NOT NULL,
  status ENUM('requested','approved','paid','rejected','cancelled') NOT NULL DEFAULT 'requested',
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_withdraw_user (user_id),
  CONSTRAINT fk_withdraw_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS disputes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL UNIQUE,
  opened_by ENUM('guest','host','admin') NOT NULL,
  reason_code VARCHAR(60) NOT NULL,
  status ENUM('open','investigating','resolved_guest','resolved_host','rejected') NOT NULL DEFAULT 'open',
  freeze_funds TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_dispute_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_user_id INT UNSIGNED NULL,
  entity_type VARCHAR(40) NOT NULL,
  entity_id VARCHAR(64) NOT NULL,
  action VARCHAR(80) NOT NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_entity (entity_type, entity_id),
  KEY idx_audit_actor (actor_user_id),
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
