/*
  LuxuryPMS additions (optional)
  - POS payments + Post-to-room support
  Run AFTER importing hotel_schema.sql
*/

-- POS payment fields
ALTER TABLE pos_orders
  ADD COLUMN amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER total_amount,
  ADD COLUMN balance_due DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER amount_paid,
  ADD COLUMN payment_status ENUM('unpaid','partial','paid','void') NOT NULL DEFAULT 'unpaid' AFTER balance_due,
  ADD COLUMN posted_folio_id INT NULL DEFAULT NULL AFTER payment_status;

-- POS payments table
CREATE TABLE IF NOT EXISTS pos_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pos_order_id INT NOT NULL,
  payment_method ENUM('cash','card','bank_transfer','room_post','other') NOT NULL DEFAULT 'cash',
  amount DECIMAL(12,2) NOT NULL,
  reference_no VARCHAR(80) NULL,
  notes TEXT NULL,
  received_by INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pos_payments_order (pos_order_id),
  KEY idx_pos_payments_created_at (created_at),
  CONSTRAINT fk_pos_payments_order FOREIGN KEY (pos_order_id) REFERENCES pos_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Room post link table
CREATE TABLE IF NOT EXISTS pos_room_charges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pos_order_id INT NOT NULL,
  folio_id INT NOT NULL,
  folio_item_id INT NULL,
  created_by INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pos_room_charges_order (pos_order_id),
  KEY idx_pos_room_charges_folio (folio_id),
  CONSTRAINT fk_pos_room_charges_order FOREIGN KEY (pos_order_id) REFERENCES pos_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_pos_room_charges_folio FOREIGN KEY (folio_id) REFERENCES folios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional FK for posted_folio_id
ALTER TABLE pos_orders
  ADD CONSTRAINT fk_pos_orders_posted_folio FOREIGN KEY (posted_folio_id) REFERENCES folios(id) ON DELETE SET NULL;