/home3/bmscom/.trash/setup.sql.1
-- Hitzone Sports Arena - Database Schema
-- Database: `bmscom_hitzone`
CREATE TABLE IF NOT EXISTS `grounds` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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,
`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
);
-- Initial Data
INSERT INTO `grounds` (`id`, `name`) VALUES (1, 'Ground 1'), (2, 'Ground 2');
-- Sample Slots for Friday & Saturday
INSERT INTO `slots` (`ground_id`, `day_of_week`, `start_time`, `end_time`, `price`) VALUES
(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),
(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);