-- =========================================================
-- 026_expenses.sql
-- Sprint 9: simple expenses module
-- =========================================================

CREATE TABLE IF NOT EXISTS expenses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  expense_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  description VARCHAR(255) NOT NULL,
  created_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_expenses_location_date (location_id, expense_date),
  CONSTRAINT fk_expenses_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;