-- Hotel PMS Database Schema
-- Encoding: utf8mb4
-- Engine: InnoDB

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','manager') NOT NULL DEFAULT 'manager',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `room_types`
--

CREATE TABLE `room_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `base_price` decimal(10,2) NOT NULL,
  `capacity_adults` int(11) NOT NULL DEFAULT 2,
  `capacity_children` int(11) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `total_rooms_count` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `rooms`
--

CREATE TABLE `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_number` varchar(20) NOT NULL,
  `type_id` int(11) NOT NULL,
  `status` enum('clean','dirty','maintenance') NOT NULL DEFAULT 'clean',
  PRIMARY KEY (`id`),
  UNIQUE KEY `room_number` (`room_number`),
  KEY `fk_room_type` (`type_id`),
  CONSTRAINT `fk_room_type` FOREIGN KEY (`type_id`) REFERENCES `room_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bookings`
--

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) NOT NULL,
  `guest_name` varchar(100) NOT NULL,
  `guest_phone` varchar(50) NOT NULL,
  `guest_email` varchar(100) DEFAULT NULL,
  `check_in` date NOT NULL,
  `check_out` date NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `status` enum('new','confirmed','checked_in','checked_out','cancelled') NOT NULL DEFAULT 'new',
  `source` enum('website','booking.com','phone','walk-in') NOT NULL DEFAULT 'website',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `color` varchar(7) DEFAULT '#7dd3fc',
  PRIMARY KEY (`id`),
  KEY `fk_booking_room` (`room_id`),
  CONSTRAINT `fk_booking_room` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `calendar_prices`
--

CREATE TABLE `calendar_prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_type_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_price_date` (`room_type_id`,`date`),
  CONSTRAINT `fk_price_room_type` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Dumping data for table `admins`
--
-- Password is 'admin123' hashed with PASSWORD_DEFAULT
--

INSERT INTO `admins` (`username`, `password_hash`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- --------------------------------------------------------

--
-- Dumping data for table `room_types`
--

INSERT INTO `room_types` (`id`, `name`, `base_price`, `capacity_adults`, `capacity_children`, `description`, `total_rooms_count`) VALUES
(1, 'Double Standard', 1500.00, 2, 0, 'Затишний двомісний номер (номери 1, 5)', 2),
(2, 'Double Comfort', 1700.00, 2, 0, 'Комфортний двомісний номер (номери 2, 6)', 2),
(3, 'Double Superior', 1700.00, 2, 0, 'Покращений двомісний номер (номери 3, 7)', 2),
(4, 'Twin', 1500.00, 2, 0, 'Номер з двома окремими ліжками (номер 4)', 1),
(5, 'Economy Double', 1400.00, 2, 0, 'Економ двомісний номер (номер 8)', 1);

-- --------------------------------------------------------

--
-- Dumping data for table `rooms`
--

INSERT INTO `rooms` (`id`, `room_number`, `type_id`, `status`) VALUES
(1, '1', 1, 'clean'),
(2, '2', 2, 'clean'),
(3, '3', 3, 'clean'),
(4, '4', 4, 'clean'),
(5, '5', 1, 'clean'),
(6, '6', 2, 'clean'),
(7, '7', 3, 'clean'),
(8, '8', 5, 'clean');

-- --------------------------------------------------------

--
-- Dumping data for table `bookings`
--

INSERT INTO `bookings` (`room_id`, `guest_name`, `guest_phone`, `guest_email`, `check_in`, `check_out`, `total_price`, `status`, `source`, `created_at`, `notes`) VALUES 
(1, 'John Doe', '+1234567890', 'john@example.com', DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 4 DAY), 300.00, 'confirmed', 'website', NOW(), 'Late check-in'),
(2, 'Jane Smith', '+0987654321', 'jane@example.com', DATE_ADD(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 3 DAY), 100.00, 'new', 'booking.com', NOW(), NULL),
(6, 'Alice Wonderland', '+1122334455', 'alice@example.com', DATE_ADD(CURDATE(), INTERVAL 5 DAY), DATE_ADD(CURDATE(), INTERVAL 10 DAY), 1000.00, 'checked_in', 'phone', NOW(), 'Allergic to cats'),
(7, 'Bob Builder', '+5544332211', 'bob@example.com', DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 2 DAY), 200.00, 'cancelled', 'walk-in', NOW(), 'Cancelled due to rain'),
(9, 'Charlie Brown', '+6677889900', 'charlie@example.com', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 DAY), 300.00, 'confirmed', 'website', NOW(), 'Request extra pillow');

COMMIT;