DataBase

-- ver.230510
-- 회원 테이블
create table member(
member_id varchar2(40) primary key,
member_pw varchar2(40) not null,
member_name varchar2(40) not null,
member_level varchar2(12) default '일반회원' not null,
member_email varchar2(100) not null,
member_gender varchar2(3) not null,
member_birth char(10) not null,
member_manner number default 36.5 not null,

check(regexp_like(member_id,'^[a-z0-9-_]{5,20}$')),

check(
regexp_like(member_pw, '[A-Z]+')    
and
regexp_like(member_pw, '[a-z]+')
and
regexp_like(member_pw, '[0-9]+')
and
regexp_like(member_pw, '[!@#$]+')
and
regexp_like(member_pw, '^[A-Za-z0-9!@#$]{8,16}$')
),
check(regexp_like(member_name, '^[가-힣]{2,10}$')),
check(instr(member_email, '@') > 0),
check(member_gender in ('남', '여')),
check(member_level in ('일반회원','매니저','관리자')),
check(member_manner >= 0)
);

-- 팀 테이블
create table team(
team_no number primary key,
team_name varchar2(30) not null,
team_day varchar2(9) not null,
team_time varchar2(30) not null,
team_city varchar2(12) not null,
team_location varchar2(30) not null,
team_gender varchar2(12) not null,
team_age varchar2(12) not null,
team_level varchar2(12) not null,
team_win number default 0 not null,
team_lose number default 0 not null,
team_schedule date not null,
team_date date DEFAULT sysdate	NOT NULL,
check(team_win >= 0),
check(team_lose >= 0)
);

-- 구장 테이블
create table ground(
ground_no number primary key,
ground_basic_addr varchar2(150) not null,
ground_detail_addr varchar2(150) not null,
ground_shower varchar2(30),
ground_park varchar2(30),
ground_size varchar2(30),
ground_name varchar2(30) not null,
ground_price number not null,
check(ground_price >= 0)
);

drop table ground;

-- 구장 시퀀스 생성
create sequence ground_no_seq;

-- 구장 예약 테이블
create table reserve(
reserve_no number primary key,
member_id references member(member_id) on delete cascade,
ground_no references ground(ground_no) on delete cascade,
payment_no references payment(payment_no) on delete cascade,
reserve_start date not null,
reserve_end date not null,
reserve_status varchar2(30) not null
);

drop table reserve;

-- 예약 시퀀스 생성
create sequence reserve_no_seq;

-- 용병 테이블
create table free(
free_no number primary key,
free_title varchar2(90) not null,
free_content varchar2(300) not null,
free_head varchar2(45) not null,
free_date date not null,
free_time varchar2(45) not null,
free_age varchar2(30) not null,
free_city varchar2(45) not null,
free_location varchar2(45) not null,
free_gender varchar2(3) not null,
free_size varchar2(45) not null,
check(free_gender in('남', '여'))
);

-- 채팅방 테이블
create table chat_room(
room_no number primary key,
room_created date default sysdate not null
);

-- 채팅방 시퀀스 생성
create sequence chat_room_seq;

-- 채팅방 참여자
create table chat_user(
room_no references chat_room(room_no) on delete cascade not null,
member_id references member(member_id) on delete set null
);

-- 이미지 테이블
create table img(
img_no number primary key,
img_name varchar2(100) not null,
img_type varchar2(60) not null,
img_size nubmer not null
);

-- 이미지 시퀀스 생성
create sequence img_no_seq;

-- 회원 이미지 테이블
create table member_img(
img_no references img(img_no) on delete cascade,
member_id references member(member_id) on delete cascade
);

-- 팀 이미지 테이블
create table team_img(
img_no references img(img_no) on delete cascade,
team_no references team(team_no) on delete cascade
);

-- 구장 이미지 테이블
create table ground_img(
img_no references img(img_no) on delete cascade,
ground_no references ground(ground_no) on delete cascade
);

drop table ground_img;

-- 팀 회원 테이블
create table team_member(
member_id references member(member_id) on delete cascade,
team_no references team(team_no) on delete cascade
);

-- 채팅방 메세지 테이블
create table chat_message (
message_no number primary key,
member_id references member(member_id) on delete set null,
room_no references chat_room(room_no) on delete cascade not null,
message_body varchar2(300) not null,
message_time date default sysdate not null
);

-- 채팅방 메세지 시퀀스 생성
create sequence chat_message_seq;

-- 팀 매칭 테이블
create table match(
match_no number primary key,
member_id references member(member_id) on delete set null,
match_title varchar2(60) not null,
match_city varchar2(30) not null,
match_location varchar2(30) not null,
match_date date not null,
match_time varchar2(60) not null,
match_age varchar2(30) not null,
match_size varchar2(30) not null,
match_status varchar2(12) not null,
team_no references team(team_no) on delete cascade,
opposing_no references team(team_no) on delete cascade,
check(match_status in('모집중', '모집마감', '경기중', '경기종료'))
);

-- 매칭 시퀀스 생성
create sequence match_no_seq;

-- 엔트리 테이블
create table entry(
entry_no number primary key,
match_no references match(match_no) on delete cascade,
team_no references team(team_no) on delete cascade,
member_id references member(member_id) on delete cascade,
team_type varchar2(16) not null,
is_selected number not null,
check(team_type in('home', 'away')),
check(is_selected in(1, 0))
);

-- 엔트리 시퀀스 생성
create sequence entry_no_seq;

-- 결제 테이블
create table payment(
payment_no number primary key,
payment_tid varchar2(20) not null,
payment_name varchar2(300) not null,
payment_total number not null,
payment_remain number not null,
payment_time date default sysdate not null,
member_id varchar2(20) not null
);

create sequence payment_seq;

-- 상품 정보 테이블
create table item(
item_no number primary key,
item_name varchar2(60) not null,
item_price number not null check(item_price >= 0),
item_discount number not null,
check(item_discount  between 0 and item_price)
);

create sequence item_seq;

-- 상세 결제 정보 테이블
create table payment_detail (
payment_detail_no number primary key,
payment_no references payment(payment_no) on delete cascade,
item_no number not null,
item_name varchar2(300) not null,
item_price number not null,
item_qty number not null
);

create sequence payment_detail_seq;

alter table payment_detail add payment_detail_status char(6) 
    check(payment_detail_status in ('완료', '취소'));
    
update payment_detail set payment_detail_status = '완료';
commit;
alter table payment_detail modify payment_detail_status not null;