-- 説明会予約システム データベース設計

-- 施設テーブル
CREATE TABLE facilities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 施設データの初期挿入
INSERT INTO facilities (name) VALUES 
('あさくら苑 / あさくら苑デイサービスセンター'),
('あさくら苑デイサービスセンター'),
('リバティーハウスあさくら');

-- 説明会スケジュールテーブル
CREATE TABLE schedules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    facility_id INT NOT NULL,
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    max_capacity INT DEFAULT 10,
    current_reservations INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (facility_id) REFERENCES facilities(id)
);

-- 予約テーブル
CREATE TABLE reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    schedule_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    name_kana VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    education VARCHAR(255) NOT NULL,
    motivation TEXT NOT NULL,
    other_notes TEXT,
    status ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
    reservation_number VARCHAR(20) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (schedule_id) REFERENCES schedules(id)
);

-- サンプルスケジュールデータ
INSERT INTO schedules (facility_id, date, start_time, end_time, max_capacity) VALUES
(1, '2025-07-27', '09:00:00', '10:00:00', 10),
(1, '2025-07-27', '10:00:00', '11:00:00', 10),
(1, '2025-07-27', '11:00:00', '12:05:00', 10),
(2, '2025-07-28', '09:00:00', '10:00:00', 8),
(2, '2025-07-28', '10:00:00', '11:00:00', 8),
(3, '2025-07-29', '09:00:00', '10:00:00', 12),
(3, '2025-07-29', '10:00:00', '11:00:00', 12);