/home3/bmscom/.trash/setup.sql.9
-- Hitzone Sports Arena - Complete Database Schema
-- Database: `bmscom_hitzone`
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `financial_logs`;
DROP TABLE IF EXISTS `bookings`;
DROP TABLE IF EXISTS `slots`;
DROP TABLE IF EXISTS `grounds`;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE `grounds` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE `slots` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`ground_id` INT NOT NULL,
`day_of_week` ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') NOT NULL,
`start_time` TIME NOT NULL,
`end_time` TIME NOT NULL,
`price` DECIMAL(10, 2) NOT NULL,
`is_active` TINYINT(1) DEFAULT 1,
FOREIGN KEY (`ground_id`) REFERENCES `grounds`(`id`) ON DELETE CASCADE
);
CREATE TABLE `bookings` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`ground_id` INT NOT NULL,
`slot_id` INT DEFAULT NULL,
`booking_date` DATE NOT NULL,
`start_time` TIME NOT NULL,
`end_time` TIME NOT NULL,
`customer_name` VARCHAR(255) NOT NULL,
`customer_phone` VARCHAR(20) NOT NULL,
`amount_paid` DECIMAL(10, 2) DEFAULT 0.00,
`status` ENUM('confirmed', 'cancelled') DEFAULT 'confirmed',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`ground_id`) REFERENCES `grounds`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`slot_id`) REFERENCES `slots`(`id`) ON DELETE SET NULL
);
CREATE TABLE `financial_logs` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`ground_id` INT NOT NULL,
`type` ENUM('income', 'expense') NOT NULL,
`amount` DECIMAL(10, 2) NOT NULL,
`hours` DECIMAL(5, 2) DEFAULT 0.00,
`description` VARCHAR(255) NOT NULL,
`entry_date` DATE NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`ground_id`) REFERENCES `grounds`(`id`) ON DELETE CASCADE
);
-- Seeding Grounds
INSERT INTO `grounds` (`id`, `name`) VALUES (1, 'Ground 1'), (2, 'Ground 2');
-- Seeding Slots for All Days of the Week
INSERT INTO `slots` (`ground_id`, `day_of_week`, `start_time`, `end_time`, `price`) VALUES
(1, 'Monday', '18:00:00', '21:00:00', 4000.00),
(1, 'Tuesday', '18:00:00', '21:00:00', 4000.00),
(1, 'Wednesday', '18:00:00', '21:00:00', 4000.00),
(1, 'Thursday', '18:00:00', '21:00:00', 4000.00),
(1, 'Friday', '15:00:00', '19:00:00', 5000.00),
(1, 'Friday', '19:00:00', '22:00:00', 6000.00),
(1, 'Saturday', '15:00:00', '19:00:00', 5000.00),
(1, 'Saturday', '19:00:00', '22:00:00', 6000.00),
(1, 'Sunday', '15:00:00', '19:00:00', 5000.00),
(1, 'Sunday', '19:00:00', '22:00:00', 6000.00),
(2, 'Monday', '18:00:00', '21:00:00', 4000.00),
(2, 'Tuesday', '18:00:00', '21:00:00', 4000.00),
(2, 'Wednesday', '18:00:00', '21:00:00', 4000.00),
(2, 'Thursday', '18:00:00', '21:00:00', 4000.00),
(2, 'Friday', '15:00:00', '19:00:00', 5000.00),
(2, 'Friday', '19:00:00', '22:00:00', 6000.00),
(2, 'Saturday', '15:00:00', '19:00:00', 5000.00),
(2, 'Saturday', '19:00:00', '22:00:00', 6000.00),
(2, 'Sunday', '15:00:00', '19:00:00', 5000.00),
(2, 'Sunday', '19:00:00', '22:00:00', 6000.00);