table

CREATE DATABASE pet;
USE pet;

CREATE TABLE address(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    state VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    street VARCHAR(255) NOT NULL
);

CREATE TABLE owner (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    password VARCHAR(255) NOT NULL,
    address_id BIGINT NOT NULL,
    name VARCHAR(30) NOT NULL,
    email VARCHAR(50) unique,
    phone VARCHAR(255) NOT NULL,
    em_phone VARCHAR(255),
    age INT,
    valid ENUM("Y","N") NOT NULL,
    created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    nickname VARCHAR(30) NOT NULL UNIQUE,
    FOREIGN KEY (address_id) REFERENCES address(id)
);

CREATE TABLE dog (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    owner_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    gender ENUM("male","female") NOT NULL,
    size ENUM("소","중","대"),
    sign_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    FOREIGN KEY (owner_id) REFERENCES owner(id)
);

CREATE TABLE trainer(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    nickname VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('male', 'female') NOT NULL,
    career INT NOT NULL DEFAULT 0
);

CREATE TABLE speciality(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    trainer_id BIGINT NOT NULL,
    FOREIGN KEY (trainer_id) REFERENCES trainer(id)
);

CREATE TABLE chat(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sender ENUM('보호자', '훈련사') NOT NULL,
    lasttime DATETIME DEFAULT CURRENT_TIMESTAMP(),
    contents VARCHAR(3000) NOT NULL,
    owner_id BIGINT,
    trainer_id BIGINT,
    FOREIGN KEY (owner_id) REFERENCES owner(id),
    FOREIGN KEY (trainer_id) REFERENCES trainer(id)
);

CREATE TABLE trainer_class (
    id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    trainer_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    fee INT NOT NULL,
    location VARCHAR(255) NOT NULL,
    class_day ENUM('월', '화', '수', '목', '금', '토', '일') NOT NULL,
    training_time ENUM('1교시', '2교시', '3교시', '4교시', '5교시', '6교시', '7교시', '8교시', '9교시') NOT NULL,
    description VARCHAR(3000) NOT NULL,
    size ENUM('대', '중', '소') NOT NULL,
    limits INT NOT NULL,
    current INT NOT NULL DEFAULT 0,
    start_date DATE NOT NULL,
    FOREIGN KEY (trainer_id) REFERENCES trainer(id)
);

CREATE TABLE owner_class(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    owner_id BIGINT NOT NULL,
    trainer_class_id BIGINT NOT NULL,
    dog_id BIGINT NOT NULL,
    classjointime DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES owner(id),
    FOREIGN KEY (trainer_class_id) REFERENCES trainer_class(id),
    FOREIGN KEY (dog_id) REFERENCES dog(id)
);

CREATE TABLE post(
    post_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    owner_class_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    contents VARCHAR(3000) NOT NULL,
    rating ENUM('1','2','3','4','5'),
    FOREIGN KEY (owner_class_id) REFERENCES owner_class(id),
    FOREIGN KEY (owner_id) REFERENCES owner(id)
);

CREATE TABLE alarm(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    trainer_class_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    FOREIGN KEY (trainer_class_id) REFERENCES trainer_class(id),
    FOREIGN KEY (owner_id) REFERENCES owner(id)
);

CREATE TABLE coupon (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    owner_id BIGINT NOT NULL,
    discount_amount INT NOT NULL,
    issue_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    expiration_date DATETIME,
    is_used ENUM('Y','N') NOT NULL DEFAULT 'N',
    code VARCHAR(255) NOT NULL UNIQUE,
    FOREIGN KEY (owner_id) REFERENCES owner(id)
);

CREATE TABLE payment(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    owner_id BIGINT NOT NULL,
    class_id BIGINT NOT NULL,
    coupon_code VARCHAR(255),
    amount INT NOT NULL,
    discount_amount INT NOT NULL DEFAULT 0,
    payment_date DATETIME DEFAULT CURRENT_TIMESTAMP(),
    FOREIGN KEY (owner_id) REFERENCES owner(id),
    FOREIGN KEY (class_id) REFERENCES owner_class(id),
    FOREIGN KEY (coupon_code) REFERENCES coupon(code)
);

SHOW TABLES;