-- 创建数据表
CREATE TABLE admin (
    admin_name VARCHAR(255) PRIMARY KEY,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE account (
    account_id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    status VARCHAR(50),
    created_by VARCHAR(255) REFERENCES admin(admin_name)
);

CREATE TABLE venue (
    venue_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    capacity INT NOT NULL CHECK (capacity > 0)
);

CREATE TABLE student (
    student_id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    personal_info_id INT UNIQUE,
    FOREIGN KEY (personal_info_id) REFERENCES personal_info(info_id)
);

CREATE TABLE coach (
    coach_id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    personal_info_id INT UNIQUE,
    FOREIGN KEY (personal_info_id) REFERENCES personal_info(info_id)
);

CREATE TABLE appointment (
    appointment_id SERIAL PRIMARY KEY,
    student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
    coach_id VARCHAR(255) NOT NULL REFERENCES coach(coach_id),
    venue_id INT NOT NULL REFERENCES venue(venue_id),
    period VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    feedback_id INT REFERENCES feedback(feedback_id)
);

CREATE TABLE feedback (
    feedback_id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    date DATE NOT NULL
);

CREATE TABLE invoice (
    invoice_id SERIAL PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL CHECK (amount >= 0),
    date DATE NOT NULL,
    appointment_id INT REFERENCES appointment(appointment_id)
);

CREATE TABLE personal_info (
    info_id SERIAL PRIMARY KEY,
    student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
    phone VARCHAR(20),
    email VARCHAR(255)
);

CREATE TABLE training_record (
    record_id SERIAL PRIMARY KEY,
    student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
    coach_id VARCHAR(255) NOT NULL REFERENCES coach(coach_id),
    feedback TEXT,
    appointment_id INT REFERENCES appointment(appointment_id)
);

CREATE TABLE booking_slot (
    slot_id SERIAL PRIMARY KEY,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL CHECK (end_time > start_time),
    venue_id INT NOT NULL REFERENCES venue(venue_id)
);