-- ============================================================
-- HotelFinder Database Schema
-- Generated from Django migrations (apps: users, hotel, payments, restaurant)
-- Target: MySQL 8.x / MariaDB 10.4+
-- Run this in MySQL Workbench: File > Open SQL Script > Execute All
-- ============================================================

CREATE DATABASE IF NOT EXISTS `hfdb`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `hfdb`;

-- ============================================================
-- DJANGO BUILT-IN TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS `django_migrations` (
  `id`      bigint       NOT NULL AUTO_INCREMENT,
  `app`     varchar(255) NOT NULL,
  `name`    varchar(255) NOT NULL,
  `applied` datetime(6)  NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `django_content_type` (
  `id`        int          NOT NULL AUTO_INCREMENT,
  `app_label` varchar(100) NOT NULL,
  `model`     varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `django_content_type_app_label_model_uniq` (`app_label`, `model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `auth_permission` (
  `id`              int          NOT NULL AUTO_INCREMENT,
  `name`            varchar(255) NOT NULL,
  `content_type_id` int          NOT NULL,
  `codename`        varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth_permission_content_type_id_codename_uniq` (`content_type_id`, `codename`),
  CONSTRAINT `auth_permission_content_type_id_fk`
    FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `auth_group` (
  `id`   int          NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `auth_group_permissions` (
  `id`            bigint NOT NULL AUTO_INCREMENT,
  `group_id`      int    NOT NULL,
  `permission_id` int    NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth_group_permissions_group_permission_uniq` (`group_id`, `permission_id`),
  CONSTRAINT `auth_group_permissions_group_id_fk`
    FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),
  CONSTRAINT `auth_group_permissions_permission_id_fk`
    FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `django_session` (
  `session_key`  varchar(40) NOT NULL,
  `session_data` longtext    NOT NULL,
  `expire_date`  datetime(6) NOT NULL,
  PRIMARY KEY (`session_key`),
  KEY `django_session_expire_date_idx` (`expire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- APP: users
-- ============================================================

CREATE TABLE IF NOT EXISTS `users_user` (
  `id`           bigint       NOT NULL AUTO_INCREMENT,
  `password`     varchar(128) NOT NULL,
  `last_login`   datetime(6)  DEFAULT NULL,
  `is_superuser` tinyint(1)   NOT NULL DEFAULT 0,
  `username`     varchar(150) NOT NULL,
  `first_name`   varchar(150) NOT NULL DEFAULT '',
  `last_name`    varchar(150) NOT NULL DEFAULT '',
  `email`        varchar(254) NOT NULL DEFAULT '',
  `is_staff`     tinyint(1)   NOT NULL DEFAULT 0,
  `is_active`    tinyint(1)   NOT NULL DEFAULT 1,
  `date_joined`  datetime(6)  NOT NULL,
  -- role choices: CUSTOMER | HOTEL_OWNER | RESTAURANT_OWNER | ADMIN | DELIVERY_PARTNER
  `role`         varchar(20)  NOT NULL DEFAULT 'CUSTOMER',
  `phone`        varchar(15)  DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users_user_groups` (
  `id`       bigint NOT NULL AUTO_INCREMENT,
  `user_id`  bigint NOT NULL,
  `group_id` int    NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_user_groups_user_group_uniq` (`user_id`, `group_id`),
  CONSTRAINT `users_user_groups_user_id_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users_user` (`id`),
  CONSTRAINT `users_user_groups_group_id_fk`
    FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users_user_user_permissions` (
  `id`            bigint NOT NULL AUTO_INCREMENT,
  `user_id`       bigint NOT NULL,
  `permission_id` int    NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_user_user_permissions_user_permission_uniq` (`user_id`, `permission_id`),
  CONSTRAINT `users_user_user_permissions_user_id_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users_user` (`id`),
  CONSTRAINT `users_user_user_permissions_permission_id_fk`
    FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users_usersession` (
  `id`            bigint      NOT NULL AUTO_INCREMENT,
  `session_key`   char(36)    NOT NULL,   -- UUID stored as string
  `ip_address`    varchar(39) DEFAULT NULL,
  `user_agent`    longtext    DEFAULT NULL,
  `created_at`    datetime(6) NOT NULL,
  `last_activity` datetime(6) NOT NULL,
  `is_active`     tinyint(1)  NOT NULL DEFAULT 1,
  `user_id`       bigint      NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_key` (`session_key`),
  CONSTRAINT `users_usersession_user_id_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users_sessionconfiguration` (
  `id`                         bigint NOT NULL AUTO_INCREMENT,
  `absolute_expiry_hours`      int    NOT NULL DEFAULT 168,
  `inactivity_timeout_minutes` int    NOT NULL DEFAULT 30,
  `max_sessions_per_user`      int    NOT NULL DEFAULT 5,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- APP: hotel
-- ============================================================

CREATE TABLE IF NOT EXISTS `hotel_hotel` (
  `id`               bigint        NOT NULL AUTO_INCREMENT,
  `name`             varchar(200)  NOT NULL,
  `city`             varchar(100)  NOT NULL,
  `address`          varchar(100)  NOT NULL,
  `description`      longtext      NOT NULL,
  `rating`           double        NOT NULL DEFAULT 0.0,
  `amenities`        longtext      NOT NULL,
  -- images: file path stored by Django ImageField (upload_to='hotel_images/')
  `images`           varchar(100)  DEFAULT NULL,
  `is_active`        tinyint(1)    NOT NULL DEFAULT 1,
  `created_at`       datetime(6)   NOT NULL,
  `owner_id`         bigint        NOT NULL,
  `checkin_time`     time(6)       NOT NULL DEFAULT '14:00:00',
  `checkout_time`    time(6)       NOT NULL DEFAULT '11:00:00',
  `branch`           varchar(100)  NOT NULL DEFAULT 'Main',
  -- pms_id: unique ID from the PMS system
  `pms_id`           int           DEFAULT NULL,
  `gstin`            varchar(20)   DEFAULT NULL,
  `pan`              varchar(20)   DEFAULT NULL,
  `pms_sync_token`   varchar(255)  DEFAULT NULL,
  `pms_sync_url`     varchar(200)  DEFAULT NULL,
  `email`            varchar(254)  DEFAULT NULL,
  `phone`            varchar(100)  DEFAULT NULL,
  `latitude`         decimal(9,6)  DEFAULT NULL,
  `longitude`        decimal(9,6)  DEFAULT NULL,
  `pms_branch_id`    int           DEFAULT NULL,
  `pms_hotel_id`     int           DEFAULT NULL,
  `country`          varchar(100)  NOT NULL DEFAULT 'India',
  `state`            varchar(100)  DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_hotel_pms_id_uniq` (`pms_id`),
  CONSTRAINT `hotel_hotel_owner_id_fk`
    FOREIGN KEY (`owner_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_roomtype` (
  `id`                       bigint        NOT NULL AUTO_INCREMENT,
  `name`                     varchar(100)  NOT NULL,
  `price`                    decimal(10,2) NOT NULL,
  `total_rooms`              int unsigned  NOT NULL,
  `hotel_id`                 bigint        NOT NULL,
  `branch`                   varchar(100)  NOT NULL DEFAULT 'Main',
  `pms_id`                   int           DEFAULT NULL,
  `demand_price_multiplier`  decimal(4,2)  NOT NULL DEFAULT 1.00,
  `demand_trigger_occupancy` double        NOT NULL DEFAULT 80.0,
  `weekend_price`            decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_roomtype_pms_id_uniq` (`pms_id`),
  CONSTRAINT `hotel_roomtype_hotel_id_fk`
    FOREIGN KEY (`hotel_id`) REFERENCES `hotel_hotel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_roomtypeimage` (
  `id`           bigint      NOT NULL AUTO_INCREMENT,
  -- image: file path (upload_to='room_type_images/')
  `image`        varchar(100) NOT NULL,
  `created_at`   datetime(6)  NOT NULL,
  `room_type_id` bigint       NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `hotel_roomtypeimage_room_type_id_fk`
    FOREIGN KEY (`room_type_id`) REFERENCES `hotel_roomtype` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_roomtypeavailability` (
  `id`              bigint       NOT NULL AUTO_INCREMENT,
  `date`            date         NOT NULL,
  `available_rooms` int unsigned NOT NULL,
  `room_type_id`    bigint       NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_roomtypeavailability_room_type_date_uniq` (`room_type_id`, `date`),
  CONSTRAINT `hotel_roomtypeavailability_room_type_id_fk`
    FOREIGN KEY (`room_type_id`) REFERENCES `hotel_roomtype` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_seasonalpricing` (
  `id`           bigint        NOT NULL AUTO_INCREMENT,
  `start_date`   date          NOT NULL,
  `end_date`     date          NOT NULL,
  `price`        decimal(10,2) NOT NULL,
  `room_type_id` bigint        NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `hotel_seasonalpricing_room_type_id_fk`
    FOREIGN KEY (`room_type_id`) REFERENCES `hotel_roomtype` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_hotelbooking` (
  `id`             bigint       NOT NULL AUTO_INCREMENT,
  `check_in`       date         NOT NULL,
  `check_out`      date         NOT NULL,
  `rooms_booked`   int unsigned NOT NULL,
  -- status choices: pending | confirmed | cancelled
  `status`         varchar(20)  NOT NULL DEFAULT 'pending',
  `created_at`     datetime(6)  NOT NULL,
  `updated_at`     datetime(6)  NOT NULL,
  `hotel_id`       bigint       NOT NULL,
  `user_id`        bigint       NOT NULL,
  `room_type_id`   bigint       NOT NULL,
  `branch`         varchar(100) NOT NULL DEFAULT 'Main',
  `email`          varchar(254) DEFAULT NULL,
  `gst_no`         varchar(20)  DEFAULT NULL,
  `guest_name`     varchar(100) DEFAULT NULL,
  `mobile`         varchar(15)  DEFAULT NULL,
  `invoice_date`   date         DEFAULT NULL,
  `invoice_number` varchar(50)  DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_hotelbooking_invoice_number_uniq` (`invoice_number`),
  CONSTRAINT `hotel_hotelbooking_hotel_id_fk`
    FOREIGN KEY (`hotel_id`) REFERENCES `hotel_hotel` (`id`),
  CONSTRAINT `hotel_hotelbooking_user_id_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users_user` (`id`),
  CONSTRAINT `hotel_hotelbooking_room_type_id_fk`
    FOREIGN KEY (`room_type_id`) REFERENCES `hotel_roomtype` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- APP: payments
-- ============================================================

CREATE TABLE IF NOT EXISTS `payments_payment` (
  `id`             bigint        NOT NULL AUTO_INCREMENT,
  `object_id`      int unsigned  NOT NULL,
  `amount`         decimal(10,2) NOT NULL,
  `transaction_id` varchar(100)  DEFAULT NULL,
  `payment_method` varchar(50)   NOT NULL DEFAULT '',
  -- status choices: pending | completed | failed
  `status`         varchar(20)   NOT NULL DEFAULT 'pending',
  `created_at`     datetime(6)   NOT NULL,
  `content_type_id` int          NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_payment_transaction_id_uniq` (`transaction_id`),
  CONSTRAINT `payments_payment_content_type_id_fk`
    FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments_commissionconfig` (
  `id`         bigint        NOT NULL AUTO_INCREMENT,
  `percentage` decimal(5,2)  NOT NULL DEFAULT 15.00,
  `created_at` datetime(6)   NOT NULL,
  `updated_at` datetime(6)   NOT NULL,
  `hotel_id`   bigint        NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_commissionconfig_hotel_id_uniq` (`hotel_id`),
  CONSTRAINT `payments_commissionconfig_hotel_id_fk`
    FOREIGN KEY (`hotel_id`) REFERENCES `hotel_hotel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments_settlement` (
  `id`                   bigint        NOT NULL AUTO_INCREMENT,
  `total_booking_amount` decimal(10,2) NOT NULL,
  `commission_amount`    decimal(10,2) NOT NULL,
  `settlement_amount`    decimal(10,2) NOT NULL,
  -- status choices: pending | processed | failed
  `status`               varchar(20)   NOT NULL DEFAULT 'pending',
  `scheduled_for`        date          NOT NULL,
  `processed_at`         datetime(6)   DEFAULT NULL,
  `transaction_id`       varchar(100)  DEFAULT NULL,
  `created_at`           datetime(6)   NOT NULL,
  `updated_at`           datetime(6)   NOT NULL,
  `booking_id`           bigint        NOT NULL,
  `hotel_id`             bigint        NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_settlement_transaction_id_uniq` (`transaction_id`),
  UNIQUE KEY `payments_settlement_booking_id_uniq` (`booking_id`),
  CONSTRAINT `payments_settlement_booking_id_fk`
    FOREIGN KEY (`booking_id`) REFERENCES `hotel_hotelbooking` (`id`),
  CONSTRAINT `payments_settlement_hotel_id_fk`
    FOREIGN KEY (`hotel_id`) REFERENCES `hotel_hotel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- APP: restaurant
-- ============================================================

CREATE TABLE IF NOT EXISTS `restaurant_restaurant` (
  `id`                      bigint        NOT NULL AUTO_INCREMENT,
  `name`                    varchar(200)  NOT NULL,
  `location`                varchar(100)  NOT NULL,
  `cuisine`                 varchar(100)  NOT NULL,
  `rating`                  double        NOT NULL DEFAULT 0.0,
  `tables_available`        int           NOT NULL DEFAULT 0,
  `created_at`              datetime(6)   NOT NULL,
  `updated_at`              datetime(6)   NOT NULL,
  `owner_id`                bigint        DEFAULT NULL,
  `address`                 longtext      NOT NULL,
  `average_cost_for_two`    decimal(10,2) NOT NULL DEFAULT 0.00,
  `closing_time`            time(6)       DEFAULT NULL,
  `delivery_radius_km`      double        NOT NULL DEFAULT 5.0,
  `description`             longtext      NOT NULL,
  -- image: file path (upload_to='restaurant_images/')
  `image`                   varchar(100)  DEFAULT NULL,
  `is_active`               tinyint(1)    NOT NULL DEFAULT 1,
  `is_delivery_available`   tinyint(1)    NOT NULL DEFAULT 1,
  `is_dine_in_available`    tinyint(1)    NOT NULL DEFAULT 1,
  `is_takeaway_available`   tinyint(1)    NOT NULL DEFAULT 1,
  `latitude`                decimal(9,6)  DEFAULT NULL,
  `longitude`               decimal(9,6)  DEFAULT NULL,
  `max_party_size`          int           NOT NULL DEFAULT 10,
  `opening_time`            time(6)       DEFAULT NULL,
  `table_booking_fee`       decimal(6,2)  NOT NULL DEFAULT 50.00,
  PRIMARY KEY (`id`),
  CONSTRAINT `restaurant_restaurant_owner_id_fk`
    FOREIGN KEY (`owner_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_menuitem` (
  `id`                        bigint        NOT NULL AUTO_INCREMENT,
  `name`                      varchar(200)  NOT NULL,
  `description`               longtext      NOT NULL,
  `price`                     decimal(8,2)  NOT NULL,
  -- category choices: STARTER|MAIN_COURSE|DESSERT|BEVERAGE|SNACKS|BIRYANI|THALI|CHINESE|SOUTH_INDIAN|NORTH_INDIAN
  `category`                  varchar(20)   NOT NULL DEFAULT 'MAIN_COURSE',
  -- image: file path (upload_to='menu_items/')
  `image`                     varchar(100)  DEFAULT NULL,
  `is_available`              tinyint(1)    NOT NULL DEFAULT 1,
  `is_vegetarian`             tinyint(1)    NOT NULL DEFAULT 0,
  `is_vegan`                  tinyint(1)    NOT NULL DEFAULT 0,
  `spice_level`               int           NOT NULL DEFAULT 0,
  `preparation_time_minutes`  int           NOT NULL DEFAULT 15,
  `created_at`                datetime(6)   NOT NULL,
  `restaurant_id`             bigint        NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `restaurant_menuitem_restaurant_id_fk`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_restaurant` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_table` (
  `id`            bigint      NOT NULL AUTO_INCREMENT,
  `table_number`  varchar(10) NOT NULL,
  `capacity`      int         NOT NULL DEFAULT 4,
  -- shape choices: ROUND | RECTANGLE | SQUARE
  `shape`         varchar(20) NOT NULL DEFAULT 'SQUARE',
  `position_x`    int         NOT NULL DEFAULT 0,
  `position_y`    int         NOT NULL DEFAULT 0,
  `is_active`     tinyint(1)  NOT NULL DEFAULT 1,
  `created_at`    datetime(6) NOT NULL,
  `restaurant_id` bigint      NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_table_restaurant_table_number_uniq` (`restaurant_id`, `table_number`),
  CONSTRAINT `restaurant_table_restaurant_id_fk`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_restaurant` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_booking` (
  `id`                       bigint        NOT NULL AUTO_INCREMENT,
  -- booking_type choices: DINE_IN | DELIVERY | TAKEAWAY
  `booking_type`             varchar(20)   NOT NULL,
  `customer_name`            varchar(100)  NOT NULL,
  `contact_number`           varchar(15)   NOT NULL,
  `email`                    varchar(254)  NOT NULL DEFAULT '',
  `reservation_date`         date          DEFAULT NULL,
  `reservation_time`         time(6)       DEFAULT NULL,
  `party_size`               int           DEFAULT NULL,
  `special_requests`         longtext      NOT NULL,
  `delivery_address`         longtext      NOT NULL,
  `delivery_latitude`        decimal(9,6)  DEFAULT NULL,
  `delivery_longitude`       decimal(9,6)  DEFAULT NULL,
  `distance_km`              double        DEFAULT NULL,
  `estimated_delivery_time`  datetime(6)   DEFAULT NULL,
  `order_notes`              longtext      NOT NULL,
  `subtotal`                 decimal(10,2) NOT NULL DEFAULT 0.00,
  `delivery_charge`          decimal(8,2)  NOT NULL DEFAULT 0.00,
  `tax_amount`               decimal(8,2)  NOT NULL DEFAULT 0.00,
  `total_amount`             decimal(10,2) NOT NULL DEFAULT 0.00,
  -- status choices: PENDING|CONFIRMED|PREPARING|READY|PARTNER_ASSIGNED|OUT_FOR_DELIVERY|PICKED_UP|DELIVERED|COMPLETED|CANCELLED
  `status`                   varchar(20)   NOT NULL DEFAULT 'PENDING',
  -- payment_status choices: UNPAID | PAID | REFUNDED
  `payment_status`           varchar(20)   NOT NULL DEFAULT 'UNPAID',
  `is_distance_warning`      tinyint(1)    NOT NULL DEFAULT 0,
  `distance_warning_message` varchar(255)  NOT NULL DEFAULT '',
  `created_at`               datetime(6)   NOT NULL,
  `updated_at`               datetime(6)   NOT NULL,
  `booking_fee`              decimal(8,2)  NOT NULL DEFAULT 0.00,
  `customer_id`              bigint        NOT NULL,
  `restaurant_id`            bigint        NOT NULL,
  `delivery_partner_id`      bigint        DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `restaurant_booking_customer_id_fk`
    FOREIGN KEY (`customer_id`) REFERENCES `users_user` (`id`),
  CONSTRAINT `restaurant_booking_restaurant_id_fk`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_restaurant` (`id`),
  CONSTRAINT `restaurant_booking_delivery_partner_id_fk`
    FOREIGN KEY (`delivery_partner_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Many-to-many: restaurant_booking <-> restaurant_table
CREATE TABLE IF NOT EXISTS `restaurant_booking_allocated_tables` (
  `id`         bigint NOT NULL AUTO_INCREMENT,
  `booking_id` bigint NOT NULL,
  `table_id`   bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_booking_allocated_tables_booking_table_uniq` (`booking_id`, `table_id`),
  CONSTRAINT `restaurant_booking_allocated_tables_booking_id_fk`
    FOREIGN KEY (`booking_id`) REFERENCES `restaurant_booking` (`id`),
  CONSTRAINT `restaurant_booking_allocated_tables_table_id_fk`
    FOREIGN KEY (`table_id`) REFERENCES `restaurant_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_bookingitem` (
  `id`                    bigint        NOT NULL AUTO_INCREMENT,
  `quantity`              int           NOT NULL DEFAULT 1,
  `unit_price`            decimal(8,2)  NOT NULL,
  `total_price`           decimal(10,2) NOT NULL,
  `special_instructions`  longtext      NOT NULL,
  `booking_id`            bigint        NOT NULL,
  `menu_item_id`          bigint        NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `restaurant_bookingitem_booking_id_fk`
    FOREIGN KEY (`booking_id`) REFERENCES `restaurant_booking` (`id`),
  CONSTRAINT `restaurant_bookingitem_menu_item_id_fk`
    FOREIGN KEY (`menu_item_id`) REFERENCES `restaurant_menuitem` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_aaharconfig` (
  `id`                          bigint       NOT NULL AUTO_INCREMENT,
  `aahar_restaurant_service_id` varchar(50)  NOT NULL,
  `aahar_domain`                varchar(255) NOT NULL,
  `is_active`                   tinyint(1)   NOT NULL DEFAULT 1,
  `linked_at`                   datetime(6)  NOT NULL,
  `updated_at`                  datetime(6)  NOT NULL,
  `restaurant_id`               bigint       NOT NULL,
  `aahar_api_token`             varchar(500) NOT NULL DEFAULT '',
  `is_multitenant`              tinyint(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_aaharconfig_restaurant_id_uniq` (`restaurant_id`),
  CONSTRAINT `restaurant_aaharconfig_restaurant_id_fk`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_restaurant` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_aaharordersync` (
  `id`                  bigint        NOT NULL AUTO_INCREMENT,
  `aahar_order_id`      int           NOT NULL,
  `aahar_order_number`  varchar(50)   NOT NULL,
  `invoice`             varchar(100)  NOT NULL,
  `room_number`         varchar(20)   NOT NULL DEFAULT '',
  `booking_id`          varchar(50)   NOT NULL DEFAULT '',
  `order_data`          json          NOT NULL,
  `grand_total`         decimal(10,2) NOT NULL DEFAULT 0.00,
  -- payment_status choices: UNPAID | PAID | REFUNDED
  `payment_status`      varchar(20)   NOT NULL DEFAULT 'UNPAID',
  `payment_mode`        varchar(20)   NOT NULL DEFAULT '',
  `synced_at`           datetime(6)   NOT NULL,
  `updated_at`          datetime(6)   NOT NULL,
  `aahar_config_id`     bigint        NOT NULL,
  `is_transfer`         varchar(50)   DEFAULT 'others',
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_aaharordersync_config_order_uniq` (`aahar_config_id`, `aahar_order_id`),
  CONSTRAINT `restaurant_aaharordersync_aahar_config_id_fk`
    FOREIGN KEY (`aahar_config_id`) REFERENCES `restaurant_aaharconfig` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_restaurantfinderconfig` (
  `id`               bigint       NOT NULL AUTO_INCREMENT,
  `brand_name`       varchar(200) NOT NULL,
  `outlet_id`        varchar(100) NOT NULL,
  `api_key`          varchar(255) NOT NULL,
  `is_active`        tinyint(1)   NOT NULL DEFAULT 1,
  `created_at`       datetime(6)  NOT NULL,
  `updated_at`       datetime(6)  NOT NULL,
  `aahar_config_id`  bigint       DEFAULT NULL,
  `api_url`          varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_restaurantfinderconfig_aahar_config_uniq` (`aahar_config_id`),
  CONSTRAINT `restaurant_restaurantfinderconfig_aahar_config_id_fk`
    FOREIGN KEY (`aahar_config_id`) REFERENCES `restaurant_aaharconfig` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_restaurantfinderordersync` (
  `id`             bigint        NOT NULL AUTO_INCREMENT,
  `rf_order_id`    varchar(100)  NOT NULL,
  `order_data`     json          NOT NULL,
  `grand_total`    decimal(10,2) NOT NULL DEFAULT 0.00,
  `payment_status` varchar(20)   NOT NULL DEFAULT 'UNPAID',
  `payment_mode`   varchar(50)   NOT NULL DEFAULT '',
  `status`         varchar(50)   NOT NULL DEFAULT 'RECEIVED',
  `synced_at`      datetime(6)   NOT NULL,
  `updated_at`     datetime(6)   NOT NULL,
  `config_id`      bigint        NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_restaurantfinderordersync_config_order_uniq` (`config_id`, `rf_order_id`),
  CONSTRAINT `restaurant_restaurantfinderordersync_config_id_fk`
    FOREIGN KEY (`config_id`) REFERENCES `restaurant_restaurantfinderconfig` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_deliverycontract` (
  `id`                   bigint        NOT NULL AUTO_INCREMENT,
  -- commission_type choices: FLAT_FEE | PERCENTAGE
  `commission_type`      varchar(20)   NOT NULL DEFAULT 'FLAT_FEE',
  `commission_value`     decimal(10,2) NOT NULL,
  -- status choices: PENDING | ACTIVE | REJECTED | INACTIVE
  `status`               varchar(20)   NOT NULL DEFAULT 'PENDING',
  `created_at`           datetime(6)   NOT NULL,
  `updated_at`           datetime(6)   NOT NULL,
  `delivery_partner_id`  bigint        NOT NULL,
  `restaurant_id`        bigint        NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_deliverycontract_restaurant_partner_uniq` (`restaurant_id`, `delivery_partner_id`),
  CONSTRAINT `restaurant_deliverycontract_delivery_partner_id_fk`
    FOREIGN KEY (`delivery_partner_id`) REFERENCES `users_user` (`id`),
  CONSTRAINT `restaurant_deliverycontract_restaurant_id_fk`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_restaurant` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_deliverycommissionledger` (
  `id`                   bigint        NOT NULL AUTO_INCREMENT,
  `commission_amount`    decimal(10,2) NOT NULL,
  `is_settled`           tinyint(1)    NOT NULL DEFAULT 0,
  `settled_at`           datetime(6)   DEFAULT NULL,
  `created_at`           datetime(6)   NOT NULL,
  `booking_id`           bigint        NOT NULL,
  `delivery_partner_id`  bigint        NOT NULL,
  `restaurant_id`        bigint        NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `restaurant_deliverycommissionledger_booking_uniq` (`booking_id`),
  CONSTRAINT `restaurant_deliverycommissionledger_booking_id_fk`
    FOREIGN KEY (`booking_id`) REFERENCES `restaurant_booking` (`id`),
  CONSTRAINT `restaurant_deliverycommissionledger_delivery_partner_id_fk`
    FOREIGN KEY (`delivery_partner_id`) REFERENCES `users_user` (`id`),
  CONSTRAINT `restaurant_deliverycommissionledger_restaurant_id_fk`
    FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_restaurant` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restaurant_deliverylocationlog` (
  `id`                   bigint       NOT NULL AUTO_INCREMENT,
  `latitude`             decimal(9,6) NOT NULL,
  `longitude`            decimal(9,6) NOT NULL,
  `timestamp`            datetime(6)  NOT NULL,
  `booking_id`           bigint       NOT NULL,
  `delivery_partner_id`  bigint       NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `restaurant_deliverylocationlog_booking_id_fk`
    FOREIGN KEY (`booking_id`) REFERENCES `restaurant_booking` (`id`),
  CONSTRAINT `restaurant_deliverylocationlog_delivery_partner_id_fk`
    FOREIGN KEY (`delivery_partner_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Django admin log (optional, needed for admin panel)
-- ============================================================

CREATE TABLE IF NOT EXISTS `django_admin_log` (
  `id`             int          NOT NULL AUTO_INCREMENT,
  `action_time`    datetime(6)  NOT NULL,
  `object_id`      longtext     DEFAULT NULL,
  `object_repr`    varchar(200) NOT NULL,
  `action_flag`    smallint unsigned NOT NULL,
  `change_message` longtext     NOT NULL,
  `content_type_id` int         DEFAULT NULL,
  `user_id`        bigint       NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `django_admin_log_content_type_id_fk`
    FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),
  CONSTRAINT `django_admin_log_user_id_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;