-- =====================================================================
-- Local schema fixes for extranet-new (branch: withoutTaxFix)
-- Purpose: the local DB dump predates this code branch. The code expects
--          several `ex_*` tables that the dump is missing. These statements
--          create them so the app runs locally.
-- DB: armyoftheload   Generated for local development only.
-- Apply with:  mysql -u root -p armyoftheload < database/local_schema_fixes.sql
-- =====================================================================

-- Audit trail written by App\Http\Middleware\Audit on every non-GET request.
-- Model: App\Audit  (insert: request_type, request_uri, params, user)
CREATE TABLE IF NOT EXISTS `ex_audits` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_type` VARCHAR(10)  NULL,
  `request_uri`  VARCHAR(255) NULL,
  `params`       TEXT         NULL,
  `user`         INT          NULL,
  `created_at`   TIMESTAMP    NULL,
  `updated_at`   TIMESTAMP    NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OTA / channel-manager credentials.
-- Model: App\Credential  (PropertyController@storeOtaCreadentials / connectedOtas)
-- Cols used in code: propertyowner_id, ota_id, user_name/username, password, status
CREATE TABLE IF NOT EXISTS `ex_otacredentials` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `propertyowner_id` INT          NULL,
  `ota_id`           INT          NULL,
  `property_id`      INT          NULL,
  `username`         VARCHAR(255) NULL,
  `user_name`        VARCHAR(255) NULL,
  `password`         VARCHAR(255) NULL,
  `hotel_code`       VARCHAR(255) NULL,
  `code`             VARCHAR(255) NULL,
  `channel`          VARCHAR(255) NULL,
  `status`           VARCHAR(100) NULL,
  `created_at`       TIMESTAMP    NULL,
  `updated_at`       TIMESTAMP    NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-booking approval log.
-- Model: App\Bookingapproval  (BookingsController@trackBookingStatus)
-- Cols used in code: booking_id, booking_status
CREATE TABLE IF NOT EXISTS `ex_bookingapprovals` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id`     INT          NULL,
  `booking_status` VARCHAR(50)  NULL,
  `created_at`     TIMESTAMP    NULL,
  `updated_at`     TIMESTAMP    NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-OTA-reservation approval log.
-- Model: App\Reservationapproval  (BookingsController@recievedOtaBooking)
-- Cols used in code: reservation_id, reservation_status
CREATE TABLE IF NOT EXISTS `ex_reservationapprovals` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reservation_id`     INT          NULL,
  `reservation_status` VARCHAR(50)  NULL,
  `created_at`         TIMESTAMP    NULL,
  `updated_at`         TIMESTAMP    NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Guest-message mirror table.
-- Model: App\Message  (no active query in main flows; created for completeness)
CREATE TABLE IF NOT EXISTS `ex_messages` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id`     VARCHAR(255) NULL,
  `reservation_id` VARCHAR(255) NULL,
  `message_body`   TEXT         NULL,
  `approved_by`    VARCHAR(255) NULL,
  `created_at`     TIMESTAMP    NULL,
  `updated_at`     TIMESTAMP    NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- Round 2: tables/columns surfaced by crawling property-context pages
-- =====================================================================

-- App\Notification (ex_notifications) is queried by start/end window on the
-- property dashboard, but the dump's table lacks those columns.
-- NOTE: MySQL has no "ADD COLUMN IF NOT EXISTS" — these are run-once statements.
ALTER TABLE `ex_notifications`
  ADD COLUMN `start_date_time` DATETIME NULL,
  ADD COLUMN `end_date_time`   DATETIME NULL;

-- App\Sentpayment (payment_send) is filtered by refference_number in FinanceController.
ALTER TABLE `payment_send`
  ADD COLUMN `refference_number` VARCHAR(255) NULL;

-- App\Reservation (OTA reservations) — queried on bookings-view by hotel_code.
-- Broad column set so SELECT */WHERE/save() used across booking flows don't break.
CREATE TABLE IF NOT EXISTS `reservations` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reservation_id`     VARCHAR(100) NULL,
  `hotel_code`         VARCHAR(100) NULL,
  `company_code`       VARCHAR(100) NULL,
  `device_type`        VARCHAR(100) NULL,
  `room_type_code`     VARCHAR(100) NULL,
  `ota_reservation_id` VARCHAR(100) NULL,
  `guest_name`         VARCHAR(255) NULL,
  `nic`                VARCHAR(100) NULL,
  `address_line`       VARCHAR(255) NULL,
  `country_name`       VARCHAR(150) NULL,
  `checkin`            DATE NULL,
  `checkout`           DATE NULL,
  `amount`             DECIMAL(12,2) NULL,
  `rooms`              INT NULL,
  `status`             VARCHAR(50) NULL,
  `reservation_status` VARCHAR(50) NULL,
  `checked_in`         VARCHAR(10) NULL,
  `viewed_by_owner`    VARCHAR(10) NULL,
  `created_at`         TIMESTAMP NULL,
  `updated_at`         TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App\DealsNew (dealsNew) — listed on deals-list via paginate().
CREATE TABLE IF NOT EXISTS `dealsNew` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT NULL,
  `coupon_id`   INT NULL,
  `title`       VARCHAR(255) NULL,
  `description` TEXT NULL,
  `discount`    DECIMAL(10,2) NULL,
  `start_date`  DATE NULL,
  `end_date`    DATE NULL,
  `status`      VARCHAR(50) NULL,
  `created_at`  TIMESTAMP NULL,
  `updated_at`  TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App\Lastminutepricedrop (lastminutepricedrops) — last-minute-price page.
-- Cols used: property_id, room_id, percentage, start_date, end_date.
CREATE TABLE IF NOT EXISTS `lastminutepricedrops` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT NULL,
  `room_id`     INT NULL,
  `percentage`  DECIMAL(10,2) NULL,
  `start_date`  DATE NULL,
  `end_date`    DATE NULL,
  `status`      VARCHAR(50) NULL,
  `created_at`  TIMESTAMP NULL,
  `updated_at`  TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Call-center requests joined onto bookings on the property dashboard.
-- DashBoardController@propertyDashboard leftJoin booking_call_center_requests.
CREATE TABLE IF NOT EXISTS `booking_call_center_requests` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id`         INT  NULL,
  `callcenter_request` TEXT NULL,
  `created_at`         TIMESTAMP NULL,
  `updated_at`         TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App\Currency (currencies) — getFlags() in Util.php looks up by country and
-- reads country_code; the dump's currencies table only had code/name/dollar_rate.
ALTER TABLE `currencies`
  ADD COLUMN `country`      VARCHAR(150) NULL,
  ADD COLUMN `country_code` VARCHAR(10)  NULL;

-- App\Parityrate (parity_rates) — DashBoardController reads the latest parity
-- update per property on the dashboard.
CREATE TABLE IF NOT EXISTS `parity_rates` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT NULL,
  `room_id`     INT NULL,
  `date`        DATE NULL,
  `rate`        DECIMAL(12,2) NULL,
  `status`      VARCHAR(50) NULL,
  `created_at`  TIMESTAMP NULL,
  `updated_at`  TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App\Commissionslab (commissionslabs) — RatesAndAvailability@calenderView reads
-- commission slabs (slab_start, slab_end, commission). Seed a few sane ranges.
CREATE TABLE IF NOT EXISTS `commissionslabs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slab_start` DECIMAL(12,2) NULL,
  `slab_end`   DECIMAL(12,2) NULL,
  `commission` DECIMAL(8,2)  NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `commissionslabs` (`slab_start`,`slab_end`,`commission`,`created_at`,`updated_at`) VALUES
  (0, 10000, 15, NOW(), NOW()),
  (10000.01, 50000, 18, NOW(), NOW()),
  (50000.01, 9999999, 20, NOW(), NOW());

-- ---------------------------------------------------------------------
-- DATA SEEDS (not schema) needed so calc-heavy pages don't divide by zero
-- or read an empty rate-code list. Local dev only.
-- ---------------------------------------------------------------------
-- bookings.usd_value was 0/NULL in the seed data -> division by zero in
-- BookingsController@view. Give it a positive exchange rate.
UPDATE `bookings` SET `usd_value` = 300 WHERE `usd_value` IS NULL OR `usd_value` = 0;

-- ratecodes was missing AI + all Refundable variants that RateService indexes
-- by ->flatten()[0]->id (calendar-view).
INSERT INTO `ratecodes` (`ratecode`,`name`,`code`,`created_at`,`updated_at`) VALUES
  ('AI','All Inclusive','AI',NOW(),NOW()),
  ('Refundable RO','Refundable Room Only','Refundable RO',NOW(),NOW()),
  ('Refundable BB','Refundable Bed & Breakfast','Refundable BB',NOW(),NOW()),
  ('Refundable HB','Refundable Half Board','Refundable HB',NOW(),NOW()),
  ('Refundable FB','Refundable Full Board','Refundable FB',NOW(),NOW()),
  ('-Refundable AI','Refundable All Inclusive','-Refundable AI',NOW(),NOW());

-- App\Receivedpayment (totalpayment_received) — FinanceController reads received
-- payments per property when building invoices/balances.
CREATE TABLE IF NOT EXISTS `totalpayment_received` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT NULL,
  `booking_id`  INT NULL,
  `amount`      DECIMAL(12,2) NULL,
  `date`        DATE NULL,
  `method`      VARCHAR(100) NULL,
  `notes`       TEXT NULL,
  `remarks`     TEXT NULL,
  `created_at`  TIMESTAMP NULL,
  `updated_at`  TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App\Referralbooking (referralbookings) — FinanceController lists referral
-- bookings per owner when building the finance invoice/balance.
CREATE TABLE IF NOT EXISTS `referralbookings` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `propertyowner_id`  INT NULL,
  `booking_id`        INT NULL,
  `booking_reference` VARCHAR(100) NULL,
  `amount`            DECIMAL(12,2) NULL,
  `commission`        DECIMAL(12,2) NULL,
  `status`            VARCHAR(50) NULL,
  `created_at`        TIMESTAMP NULL,
  `updated_at`        TIMESTAMP NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
