CREATE DATABASE IF NOT EXISTS hotel_sona CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hotel_sona;

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,username VARCHAR(80) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,role ENUM('manager','receptionist') NOT NULL DEFAULT 'receptionist',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE room_types (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,description TEXT,base_rate DECIMAL(10,2) NOT NULL DEFAULT 0,extra_bed_rate DECIMAL(10,2) NOT NULL DEFAULT 0,extra_person_rate DECIMAL(10,2) NOT NULL DEFAULT 0,parking_rate DECIMAL(10,2) NOT NULL DEFAULT 0,max_adults INT NOT NULL DEFAULT 2,max_children INT NOT NULL DEFAULT 1,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE rooms (id INT AUTO_INCREMENT PRIMARY KEY,room_no VARCHAR(20) UNIQUE NOT NULL,floor VARCHAR(50),room_type_id INT NOT NULL,status ENUM('available','maintenance') DEFAULT 'available',active TINYINT(1) NOT NULL DEFAULT 1,notes TEXT,FOREIGN KEY(room_type_id) REFERENCES room_types(id));
CREATE TABLE guests (id INT AUTO_INCREMENT PRIMARY KEY,surname VARCHAR(80),first_name VARCHAR(100) NOT NULL,mobile VARCHAR(30),dob DATE,nationality VARCHAR(80),occupation VARCHAR(100),designation VARCHAR(100),company VARCHAR(150),permanent_address TEXT,relation VARCHAR(100),next_destination VARCHAR(150),purpose ENUM('Personal','Tour','Other') DEFAULT 'Personal',id_proof_type VARCHAR(60),id_proof_number VARCHAR(100),id_proof_file VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE vehicles (id INT AUTO_INCREMENT PRIMARY KEY,guest_id INT,vehicle_no VARCHAR(50),parking_required TINYINT(1) DEFAULT 0,FOREIGN KEY(guest_id) REFERENCES guests(id) ON DELETE CASCADE);
CREATE TABLE bookings (id INT AUTO_INCREMENT PRIMARY KEY,booking_no VARCHAR(30) UNIQUE NOT NULL,guest_id INT NOT NULL,arrival_date DATE NOT NULL,arrival_time TIME,departure_date DATE NOT NULL,departure_time TIME,actual_checkout_date DATE NULL,actual_checkout_time TIME NULL,status ENUM('reserved','checked_in','checked_out','cancelled') DEFAULT 'reserved',num_gents INT DEFAULT 0,num_ladies INT DEFAULT 0,num_children INT DEFAULT 0,advance_amount DECIMAL(10,2) DEFAULT 0,notes TEXT,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(guest_id) REFERENCES guests(id),FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE booking_rooms (id INT AUTO_INCREMENT PRIMARY KEY,booking_id INT NOT NULL,room_id INT NOT NULL,from_date DATE NOT NULL,to_date DATE NOT NULL,rate DECIMAL(10,2) NOT NULL,room_type_name VARCHAR(100) NOT NULL,persons INT NOT NULL DEFAULT 1,adults INT NOT NULL DEFAULT 1,children INT NOT NULL DEFAULT 0,extra_bed_rate DECIMAL(10,2) NOT NULL DEFAULT 0,extra_person_rate DECIMAL(10,2) NOT NULL DEFAULT 0,parking_rate DECIMAL(10,2) NOT NULL DEFAULT 0,extra_bed_qty INT DEFAULT 0,extra_person_qty INT DEFAULT 0,parking_qty INT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,FOREIGN KEY(room_id) REFERENCES rooms(id));
CREATE TABLE payments (id INT AUTO_INCREMENT PRIMARY KEY,booking_id INT NOT NULL,amount DECIMAL(10,2) NOT NULL,payment_type ENUM('advance','balance','other') DEFAULT 'balance',method ENUM('cash','upi','card','bank','other') DEFAULT 'cash',reference_no VARCHAR(100),paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,created_by INT,FOREIGN KEY(booking_id) REFERENCES bookings(id));
CREATE TABLE refunds (id INT AUTO_INCREMENT PRIMARY KEY,booking_id INT NOT NULL,amount DECIMAL(10,2) NOT NULL,reason TEXT,method ENUM('cash','upi','card','bank','other') DEFAULT 'cash',refunded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,created_by INT,FOREIGN KEY(booking_id) REFERENCES bookings(id));
CREATE TABLE invoices (id INT AUTO_INCREMENT PRIMARY KEY,invoice_no VARCHAR(30) UNIQUE NOT NULL,booking_id INT NOT NULL,subtotal DECIMAL(10,2) NOT NULL,discount DECIMAL(10,2) DEFAULT 0,taxable DECIMAL(10,2) NOT NULL,gst_rate DECIMAL(5,2) DEFAULT 0,cgst DECIMAL(10,2) DEFAULT 0,sgst DECIMAL(10,2) DEFAULT 0,total DECIMAL(10,2) NOT NULL,advance_paid DECIMAL(10,2) DEFAULT 0,balance_due DECIMAL(10,2) DEFAULT 0,refund_amount DECIMAL(10,2) DEFAULT 0,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(booking_id) REFERENCES bookings(id));
CREATE TABLE additional_charges (id INT AUTO_INCREMENT PRIMARY KEY,booking_id INT NOT NULL,invoice_id INT NULL,description VARCHAR(150) NOT NULL,amount DECIMAL(10,2) NOT NULL,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE room_blocks (id INT AUTO_INCREMENT PRIMARY KEY,room_id INT NOT NULL,block_from DATE NOT NULL,block_to DATE NOT NULL,reason VARCHAR(120) NOT NULL DEFAULT 'Maintenance',notes TEXT,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,INDEX idx_room_block_dates(room_id,block_from,block_to));
CREATE TABLE audit_logs (id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT,action VARCHAR(120),entity VARCHAR(80),entity_id INT,details TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE settings (setting_key VARCHAR(100) PRIMARY KEY, setting_value VARCHAR(255) NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO settings(setting_key,setting_value) VALUES ('gst_rate','18.00') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

INSERT INTO room_types(name,description,base_rate,extra_bed_rate,extra_person_rate,parking_rate,max_adults,max_children) VALUES
('Ground Floor','Standard ground floor room',1500,500,300,200,2,1),
('Sea Facing','Sea facing room',2500,500,300,200,3,2),
('Deluxe','Deluxe room',3000,700,400,200,4,2);
INSERT INTO rooms(room_no,floor,room_type_id) VALUES ('101','Ground Floor',1),('102','Ground Floor',1),('103','Ground Floor',1),('201','First Floor',2),('202','First Floor',2),('203','First Floor',2),('301','Second Floor',3),('302','Second Floor',3);
INSERT INTO users(name,username,password_hash,role) VALUES ('Hotel Manager','manager','$2y$12$LOy0F2qqPTD6j10veVoR9ufu6UPGhxHa1BM4mNfSu2UintBryf2NK','manager'),('Front Desk','reception','$2y$12$LOy0F2qqPTD6j10veVoR9ufu6UPGhxHa1BM4mNfSu2UintBryf2NK','receptionist');