-- =============================================
-- Rent Boat Cannobio - Simple Database Creation
-- For EMPTY database only
-- =============================================

CREATE DATABASE IF NOT EXISTS rentygdu_boat_rental;
USE rentygdu_boat_rental;

-- =============================================
-- Table: boats
-- =============================================
CREATE TABLE `boats` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `capacity` INT(11) NOT NULL,
  `hourly_rate` DECIMAL(10,2) NOT NULL,
  `description` TEXT,
  `image_url` VARCHAR(255),
  `status` ENUM('available', 'rented', 'maintenance') DEFAULT 'available',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- Table: bookings
-- =============================================
CREATE TABLE `bookings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `boat_id` INT(11) NOT NULL,
  `booking_date` DATE NOT NULL,
  `start_time` TIME NOT NULL,
  `duration` DECIMAL(3,1) NOT NULL,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_email` VARCHAR(255),
  `customer_phone` VARCHAR(50) NOT NULL,
  `passengers` INT(11) DEFAULT 1,
  `payment_status` ENUM('pending', 'paid', 'cash', 'confirmed', 'closed', 'cancelled') DEFAULT 'pending',
  `payment_method` VARCHAR(50),
  `total_amount` DECIMAL(10,2) DEFAULT 0.00,
  `booking_reference` VARCHAR(50),
  `special_requests` TEXT,
  `contract_name` VARCHAR(100),
  `contract_surname` VARCHAR(100),
  `contract_address` VARCHAR(255),
  `contract_postcode` VARCHAR(20),
  `contract_city` VARCHAR(100),
  `contract_passport` VARCHAR(50),
  `contract_passport_type` VARCHAR(50),
  `contract_tel` VARCHAR(50),
  `signature` LONGTEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `booking_reference` (`booking_reference`),
  KEY `boat_id` (`boat_id`),
  KEY `booking_date` (`booking_date`),
  CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`boat_id`) REFERENCES `boats` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- Sample boats data
-- =============================================
INSERT INTO `boats` (`name`, `type`, `capacity`, `hourly_rate`, `description`, `status`) VALUES
('ARTEMIDE 17', 'Open', 6, 60.00, 'Comfortable open boat perfect for families', 'available'),
('EDEN 18', 'Open', 7, 70.00, 'Spacious boat with excellent stability', 'available'),
('FISHERMAN 17', 'Open', 6, 65.00, 'Ideal for fishing trips and water sports', 'available'),
('LADY 17', 'Open', 5, 55.00, 'Elegant and easy to handle', 'available');
