-- =====================================================================
-- Travel Docket · Migration 004 · Phase P4 — Expenses and settlement
-- Covers: EXP-01..11, DOC-08, RPT-02, RPT-03, PWA-05 (client uuid = idempotency key)
-- Rules enforced in the service layer (same transaction):
--   SUM(expense_payments.amount) = expenses.amount
--   SUM(expense_shares.amount)   = expenses.amount (remainder to one traveller)
--   kitty travellers never receive shares
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE expenses (
  id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid                 CHAR(36) CHARACTER SET ascii NOT NULL,
  trip_id              BIGINT UNSIGNED NOT NULL,
  booking_id           BIGINT UNSIGNED NULL,           -- fare expense (EXP-02)
  category             ENUM('transport','stay','food','tickets','shopping','other') NOT NULL DEFAULT 'other',
  description          VARCHAR(200)  NOT NULL,
  amount               DECIMAL(12,2) NOT NULL,
  currency             CHAR(3)       NOT NULL DEFAULT 'INR',
  fx_rate              DECIMAL(14,6) NOT NULL DEFAULT 1.000000, -- to trip base currency (EXP-10)
  base_amount          DECIMAL(12,2) NOT NULL,                   -- amount × fx_rate
  expense_date         DATE          NOT NULL,
  place_id             BIGINT UNSIGNED NULL,
  split_type           ENUM('equal','exact','percent','shares') NOT NULL DEFAULT 'equal',
  notes                VARCHAR(500)  NULL,
  receipt_document_id  BIGINT UNSIGNED NULL,           -- EXP-11 / DOC-08
  created_by           BIGINT UNSIGNED NULL,
  created_at           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at           DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_expenses_uuid (uuid),
  KEY ix_expenses_trip_date (trip_id, expense_date),
  KEY ix_expenses_booking (booking_id),
  KEY ix_expenses_category (trip_id, category),
  CONSTRAINT chk_exp_amount CHECK (amount > 0),
  CONSTRAINT fk_exp_trip    FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_exp_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE SET NULL,
  CONSTRAINT fk_exp_place   FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE SET NULL,
  CONSTRAINT fk_exp_receipt FOREIGN KEY (receipt_document_id) REFERENCES documents (id) ON DELETE SET NULL,
  CONSTRAINT fk_exp_user    FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Who paid, how (EXP-03). traveller may be the kitty (EXP-06).
CREATE TABLE expense_payments (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          CHAR(36) CHARACTER SET ascii NOT NULL,
  expense_id    BIGINT UNSIGNED NOT NULL,
  traveller_id  BIGINT UNSIGNED NOT NULL,
  amount        DECIMAL(12,2) NOT NULL,
  mode          ENUM('upi','card','cash','netbanking','wallet','kitty','other') NOT NULL DEFAULT 'upi',
  reference     VARCHAR(80)   NULL,                   -- UPI txn id / card last 4 / note
  paid_at       DATETIME      NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_exp_pay_uuid (uuid),
  KEY ix_exp_pay_expense (expense_id),
  KEY ix_exp_pay_traveller (traveller_id),
  CONSTRAINT chk_exp_pay_amount CHECK (amount > 0),
  CONSTRAINT fk_exp_pay_expense   FOREIGN KEY (expense_id) REFERENCES expenses (id) ON DELETE CASCADE,
  CONSTRAINT fk_exp_pay_traveller FOREIGN KEY (traveller_id) REFERENCES travellers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Who owes what (EXP-04). amount is always resolved; percent/units kept for editing.
CREATE TABLE expense_shares (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  expense_id    BIGINT UNSIGNED NOT NULL,
  traveller_id  BIGINT UNSIGNED NOT NULL,
  amount        DECIMAL(12,2) NOT NULL,
  percent       DECIMAL(6,3)  NULL,
  units         DECIMAL(6,2)  NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_exp_share (expense_id, traveller_id),
  KEY ix_exp_share_traveller (traveller_id),
  CONSTRAINT chk_exp_share_amount CHECK (amount >= 0),
  CONSTRAINT fk_exp_share_expense   FOREIGN KEY (expense_id) REFERENCES expenses (id) ON DELETE CASCADE,
  CONSTRAINT fk_exp_share_traveller FOREIGN KEY (traveller_id) REFERENCES travellers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Refunds / cancellation credits back to a payer (EXP-09).
-- Balance logic: payer's paid reduced by amount; shares reduced proportionally.
CREATE TABLE expense_refunds (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          CHAR(36) CHARACTER SET ascii NOT NULL,
  expense_id    BIGINT UNSIGNED NOT NULL,
  traveller_id  BIGINT UNSIGNED NOT NULL,             -- who received the refund
  amount        DECIMAL(12,2) NOT NULL,
  mode          ENUM('upi','card','cash','netbanking','wallet','kitty','other') NOT NULL DEFAULT 'upi',
  reference     VARCHAR(80)   NULL,
  refunded_on   DATE          NOT NULL,
  created_by    BIGINT UNSIGNED NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at    DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_exp_refund_uuid (uuid),
  KEY ix_exp_refund_expense (expense_id),
  CONSTRAINT chk_exp_refund_amount CHECK (amount > 0),
  CONSTRAINT fk_exp_ref_expense   FOREIGN KEY (expense_id) REFERENCES expenses (id) ON DELETE CASCADE,
  CONSTRAINT fk_exp_ref_traveller FOREIGN KEY (traveller_id) REFERENCES travellers (id),
  CONSTRAINT fk_exp_ref_user      FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Transfers between travellers (EXP-08). A contribution to the kitty is a
-- settlement with to_traveller_id = the kitty traveller.
CREATE TABLE settlements (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid               CHAR(36) CHARACTER SET ascii NOT NULL,
  trip_id            BIGINT UNSIGNED NOT NULL,
  from_traveller_id  BIGINT UNSIGNED NOT NULL,
  to_traveller_id    BIGINT UNSIGNED NOT NULL,
  amount             DECIMAL(12,2) NOT NULL,
  currency           CHAR(3)       NOT NULL DEFAULT 'INR',
  mode               ENUM('upi','card','cash','netbanking','wallet','other') NOT NULL DEFAULT 'upi',
  reference          VARCHAR(80)   NULL,
  settled_on         DATE          NOT NULL,
  notes              VARCHAR(255)  NULL,
  created_by         BIGINT UNSIGNED NULL,
  created_at         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at         DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_settlements_uuid (uuid),
  KEY ix_settle_trip (trip_id, settled_on),
  KEY ix_settle_from (from_traveller_id),
  KEY ix_settle_to (to_traveller_id),
  CONSTRAINT chk_settle_amount CHECK (amount > 0),
  CONSTRAINT chk_settle_parties CHECK (from_traveller_id <> to_traveller_id),
  CONSTRAINT fk_settle_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_settle_from FOREIGN KEY (from_traveller_id) REFERENCES travellers (id),
  CONSTRAINT fk_settle_to   FOREIGN KEY (to_traveller_id) REFERENCES travellers (id),
  CONSTRAINT fk_settle_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO schema_migrations (version) VALUES ('004_p4_expenses');