-- =====================================================
-- HandBrakeCars Database Schema
-- handbrakecars.com
-- =====================================================


-- =====================================================
-- 4. CARS (العربيات)
-- =====================================================
CREATE TABLE `cars` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `owner_id`     INT UNSIGNED NOT NULL,
  `added_by`     ENUM('owner','admin') DEFAULT 'owner',
  `brand`        VARCHAR(100) NOT NULL,
  `model`        VARCHAR(100) NOT NULL,
  `year`         YEAR NOT NULL,
  `type`         ENUM('sedan','suv','luxury','van','truck','other') NOT NULL,
  `color`        VARCHAR(50) DEFAULT NULL,
  `plate_no`     VARCHAR(30) DEFAULT NULL,
  `seats`        TINYINT DEFAULT 5,
  `fuel`         ENUM('petrol','diesel','electric','hybrid') DEFAULT 'petrol',
  `transmission` ENUM('automatic','manual') DEFAULT 'automatic',
  `price_day`    DECIMAL(10,2) NOT NULL,
  `price_week`   DECIMAL(10,2) DEFAULT NULL,
  `price_month`  DECIMAL(10,2) DEFAULT NULL,
  `location`     VARCHAR(150) DEFAULT NULL,
  `description`  TEXT DEFAULT NULL,
  `img_main`     VARCHAR(255) DEFAULT NULL,
  `img_2`        VARCHAR(255) DEFAULT NULL,
  `img_3`        VARCHAR(255) DEFAULT NULL,
  `img_4`        VARCHAR(255) DEFAULT NULL,
  `status`       ENUM('available','rented','maintenance') DEFAULT 'available',
  `approved`     TINYINT(1) DEFAULT 0,
  `views`        INT DEFAULT 0,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `cars`
  ADD CONSTRAINT `fk_cars_owner`
  FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON DELETE CASCADE;

-- =====================================================
-- 5. BOOKINGS (الحجوزات)
-- =====================================================
CREATE TABLE `bookings` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `booking_ref`    VARCHAR(20) NOT NULL UNIQUE,
  `user_id`        INT UNSIGNED NOT NULL,
  `car_id`         INT UNSIGNED NOT NULL,
  `owner_id`       INT UNSIGNED NOT NULL,
  `pickup_date`    DATE NOT NULL,
  `return_date`    DATE NOT NULL,
  `total_days`     INT NOT NULL,
  `price_per_day`  DECIMAL(10,2) NOT NULL,
  `subtotal`       DECIMAL(10,2) NOT NULL,
  `commission_pct` DECIMAL(5,2)  NOT NULL DEFAULT 10.00,
  `commission_amt` DECIMAL(10,2) NOT NULL,
  `owner_earning`  DECIMAL(10,2) NOT NULL,
  `payment_method` ENUM('stripe','paypal','cash') DEFAULT 'cash',
  `payment_status` ENUM('pending','paid','refunded') DEFAULT 'pending',
  `status`         ENUM('pending','active','completed','cancelled') DEFAULT 'pending',
  `notes`          TEXT DEFAULT NULL,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `bookings`
  ADD CONSTRAINT `fk_bookings_user`  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`)  ON DELETE CASCADE,
  ADD CONSTRAINT `fk_bookings_car`   FOREIGN KEY (`car_id`)   REFERENCES `cars`(`id`)   ON DELETE CASCADE,
  ADD CONSTRAINT `fk_bookings_owner` FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON DELETE CASCADE;

-- =====================================================
-- 6. BRANCHES (الفروع)
-- =====================================================
CREATE TABLE `branches` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(150) NOT NULL,
  `address`    VARCHAR(255) DEFAULT NULL,
  `city`       VARCHAR(100) DEFAULT NULL,
  `phone`      VARCHAR(20)  DEFAULT NULL,
  `lat`        DECIMAL(10,7) DEFAULT NULL,
  `lng`        DECIMAL(10,7) DEFAULT NULL,
  `hours`      VARCHAR(100) DEFAULT NULL,
  `status`     ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `branches` (`name`, `address`, `city`, `hours`) VALUES
('Cairo — Nasr City', 'Mostafa El-Nahas St, Nasr City', 'Cairo', '9am - 10pm'),
('Cairo — Maadi',     'Road 9, Maadi',                  'Cairo', '8am - 11pm'),
('Alexandria',        'El-Corniche, Sidi Gaber',         'Alex',  '9am - 9pm'),
('Giza — 6th October','Central Axis, 6th October',       'Giza',  '10am - 8pm');

-- =====================================================
-- 7. SETTINGS (إعدادات الموقع)
-- =====================================================
CREATE TABLE `settings` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key`  VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name',        'HandBrakeCars'),
('commission_rate',  '10'),
('currency',         'EGP'),
('contact_email',    'info@handbrakecars.com'),
('contact_phone',    '+20 100 000 0000');

-- =====================================================
-- 8. NOTIFICATIONS (الإشعارات)
-- =====================================================
CREATE TABLE `notifications` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `to_type`    ENUM('admin','owner','user') NOT NULL,
  `to_id`      INT UNSIGNED NOT NULL,
  `message`    TEXT NOT NULL,
  `link`       VARCHAR(255) DEFAULT NULL,
  `is_read`    TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `cars`      ADD INDEX `idx_status`  (`status`, `approved`);
ALTER TABLE `cars`      ADD INDEX `idx_type`    (`type`);
ALTER TABLE `bookings`  ADD INDEX `idx_user`    (`user_id`);
ALTER TABLE `bookings`  ADD INDEX `idx_status`  (`status`);
ALTER TABLE `bookings`  ADD INDEX `idx_ref`     (`booking_ref`);
