create table TOWN(
town_no number(10) ,
town_gu varchar2(10),
town_dong varchar2(10),
town_latitude number(10),
town_longitude number(10),
CONSTRAINT TOWN_PK PRIMARY KEY (town_no)
);

ALTER TABLE TOWN
MODIFY (TOWN_LATITUDE NUMBER(10,7));

ALTER TABLE TOWN
MODIFY (TOWN_LONGITUDE NUMBER(10,7));

create sequence town_seq_puching_town_no;

alter table TOWN MODIFY  town_gu varchar2(10 char); 
alter table TOWN MODIFY  town_dong varchar2(10 char); 
commit;

create table MEMBER(
member_no number(10) ,
town_no number(10),
member_email varchar2(20 char),
member_password varchar2(60 char),
member_nickname varchar2(10 char),
member_phone varchar2(13 char),
member_latitude number(10),
member_longitude number(10),
member_dog_sign_enabled number(1),
member_enabled number(1),
member_login_dount number(7),
member_question_select_count number(7),
member_location_enabled number(1),
CONSTRAINT MEMBER_PK PRIMARY KEY (member_no),
 CONSTRAINT MEMBER_FK FOREIGN KEY(town_no) REFERENCES TOWN(town_no)
);

create sequence member_seq_member_no;

ALTER TABLE MEMBER
MODIFY (MEMBER_LATITUDE NUMBER(10,7));

ALTER TABLE MEMBER
MODIFY (MEMBER_LONGITUDE NUMBER(10,7));

ALTER TABLE member ADD member_profile_image varchar2(100 char);

ALTER TABLE member ADD member_point number(7);
commit;

ALTER TABLE MEMBER RENAME COLUMN member_login_dount TO member_login_count;

create table FOLLOW(
follow_no number(10) ,
member_no number(10),
follow_member_no number(10),
CONSTRAINT FOLLOW_PK PRIMARY KEY(follow_no),
 CONSTRAINT FOLLOW_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

create sequence follow_seq_follow_no;

create table ALARM(
alarm_no number(10) ,
member_no number(10),
alarm_content varchar2(200 char),
alarm_rink varchar2(200 char),
alarm_read_enabled number(1),
CONSTRAINT ALARM_PK PRIMARY KEY(alarm_no,member_no),
 CONSTRAINT ALARM_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

ALTER TABLE alarm RENAME COLUMN alarm_rink TO alarm_link;

create sequence alarm_seq_alarm_no;

create table REPORT(
report_no number(10),
member_no number(10),
report_category varchar2(10 char),
report_member_no number(10),
report_content varchar2(200 char),
report_date date,
CONSTRAINT REPORT_PK PRIMARY KEY(report_no,member_no),
 
 CONSTRAINT REPORT_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

create sequence report_seq_report_no;

create table BLOCK(
block_no number(10),
member_no number(10),
report_no number(10),
block_date date,
block_deadline_date date, 
CONSTRAINT BLOCK_PK PRIMARY KEY(block_no),
CONSTRAINT BLOCK_FK FOREIGN KEY(report_no,member_no) REFERENCES REPORT(report_no,member_no)
);

create sequence block_seq_block_no;

create table DOG(
dog_no number(10) ,
member_no number(10),
dog_birthday_date date,
dog_introduce varchar2(300 char),
dog_gender number(1),
dog_not_gender_enabled number(1),
dog_weight number(7),
dog_category varchar2(20 char),
dog_name varchar2(10 char),
dog_profile varchar2(100 char),
 CONSTRAINT DOG_PK PRIMARY KEY(dog_no,member_no),
CONSTRAINT DOG_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

create sequence dog_seq_dog_no;

CREATE TABLE BOARD (
	board_no number(7,0) ,
  town_no  number(10,0),
	member_no number(10,0),
  board_title varchar2(100 char),
  board_content varchar2(4000 char),
  board_write_date date,
  board_read_count number(6,0),
  board_recommend_count number(6,0),
  board_enabled number(1,0),
  CONSTRAINT BOARD_PK PRIMARY KEY(board_no),
  CONSTRAINT BOARD_FK_TOWN FOREIGN KEY(town_no) REFERENCES TOWN(town_no),
  CONSTRAINT BOARD_FK_MEMBER FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

CREATE SEQUENCE SEQ_BOARD_NO;

CREATE TABLE BOARD_COMMENT(
	board_no number(7,0),
  comment_no number(7,0),
  member_no number(10,0),
  comment_content varchar2(400 char),
  comment_write_date date,
  comment_enabled number(1,0),
  CONSTRAINT BOARD_COMMENT_PK PRIMARY KEY (board_no, comment_no),
  CONSTRAINT BOARD_COMMENT_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)

);

CREATE SEQUENCE SEQ_BOARD_COMMENT_NO;

CREATE TABLE COMMENT_SIDE (
board_no number(7,0),
comment_no number(7,0),
comment_side_no number(7,0),
member_no number(10,0),
comment_side_content varchar2(400 char),
comment_side_write_date date,
comment_side_enabled number(1),
CONSTRAINT  COMMENT_SIDE_PK PRIMARY KEY(board_no, comment_no, comment_side_no),
  CONSTRAINT COMMENT_SIDE_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

create sequence comment_side_seq_comment_side_no;

CREATE TABLE KNOWLEDGE_QUESTION (
	knowledge_question_no number(7,0),
  member_no number(10,0),
  knowledge_question_title varchar2(100 char),
  knowledge_question_content varchar2(4000 char),
  knowledge_question_read_count number(6,0),
  knowledge_question_write_date date,
  knowledge_question_point number(4,0),
  CONSTRAINT KNOWLEDGE_QUESTION_PK PRIMARY KEY (knowledge_question_no),
  CONSTRAINT KNOWLEDGE_QUESTION_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

create sequence knowledge_question_seq_knowledge_question_no;

CREATE TABLE KNOWLEDGE_ANSWER (
knowledge_question_no number(7,0),	
knowledge_answer_no number(7,0),
member_no number(10,0),
knowledge_answer_content varchar2(4000 char),
knowledge_answer_write_date date,
knowledge_answer_selection_enabled number(1),
knowledge_answer_recommend number(6,0),

CONSTRAINT KNOWLEDGE_ANSWER_PK PRIMARY KEY (knowledge_question_no, knowledge_answer_no),
 CONSTRAINT KNOWLEDGE_ANSWER_FK FOREIGN KEY(member_no) REFERENCES MEMBER(member_no)
);

create sequence knowledge_answer_seq_knowledge_answer_no;

CREATE TABLE BOARD_NOTICE (
board_notice_no number(7,0),
board_notice_writer varchar2(50 char),
board_notice_title varchar2(100 char),
board_notice_content varchar2(400 char),
board_notice_write_date date,
board_notice_read_count number(6,0),
CONSTRAINT BOARD_NOTICE_PK PRIMARY KEY (board_notice_no)
);

create sequence board_notice_seq_board_notice_no;

CREATE TABLE BOARD_IMAGE (
board_no number(7,0),
board_image_no number(7,0),
board_image_filename varchar2(100 char),
CONSTRAINT BOARD_IMAGE_PK PRIMARY KEY(board_no, board_image_no)
);

create table PRODUCT_CATEGORY (
	product_category_no number(7),
	product_category_name varchar2(20 char),
	CONSTRAINT PRODUCT_CATEGORY_PK PRIMARY KEY(PRODUCT_CATEGORY_NO)
);

create table PRODUCT (
	product_no number(7),
	product_category_no number(7),
	product_name varchar2(50 char),
	product_price number(7),
	product_stock number(7),
	product_grade number(7),
	product_content clob,
	product_write_date date,
	CONSTRAINT PRODUCT_PK PRIMARY KEY(PRODUCT_NO),
	CONSTRAINT PRODUCT_FK	FOREIGN KEY(PRODUCT_CATEGORY_NO) REFERENCES PRODUCT_CATEGORY(PRODUCT_CATEGORY_NO)
);

create sequence product_seq_product_no;

create table PRODUCT_IMAGE (
	product_no number(7),
	product_image_no number(7),
	product_image_filename varchar2(50 char),
	CONSTRAINT PRODUCT_IMAGE_PK PRIMARY KEY(PRODUCT_NO, PRODUCT_IMAGE_NO)
);

create sequence product_image_seq_product_image_no;

create table ADDRESS (
	address_no number(7),
	member_no number(7),
	address_name varchar2(10 char),
	address_postcode varchar2(10 char),
	address_address varchar2(50 char),
	address_address_detail varchar2(50 char),
	CONSTRAINT ADDRESS_PK PRIMARY KEY(ADDRESS_NO, MEMBER_NO)
);

create sequence address_seq_address_no;

create table FAVORITE (
	favorite_no number(7),
	member_no number(7),
	product_no number(7),
	CONSTRAINT FAVORITE_PK PRIMARY KEY(FAVORITE_NO, MEMBER_NO),
	CONSTRAINT FAVORITE_FK FOREIGN KEY(PRODUCT_NO) REFERENCES PRODUCT(PRODUCT_NO)
);

create sequence favorite_seq_favorite_no;

create table PRODUCT_ORDER (
	product_order_no number(7),
	member_no number(7),
	address_no number(7),
	product_order_total_price number(8),
	product_order_date date,
	CONSTRAINT PRODUCT_ORDER_PK PRIMARY KEY(PRODUCT_ORDER_NO),
	CONSTRAINT PRODUCT_ORDER_FK FOREIGN KEY(ADDRESS_NO,MEMBER_NO) REFERENCES ADDRESS(ADDRESS_NO,MEMBER_NO)
);

create sequence product_order_seq_product_order_no;

create table PRODUCT_ORDER_DETAIL (
	product_order_detail_no number(7),
	product_order_no number(7),
	product_no number(7),
	product_order_detail_count number(7),
	product_order_detail_price number(7),
	product_order_detail_review_enabled number(1),
	CONSTRAINT ORDER_DETAIL_PK PRIMARY KEY(PRODUCT_ORDER_DETAIL_NO, PRODUCT_ORDER_NO),
	CONSTRAINT ORDER_DETAIL_FK FOREIGN KEY(PRODUCT_NO) REFERENCES PRODUCT(PRODUCT_NO)
);

create sequence product_order_detail_seq_product_order_detail_no;

create table PRODUCT_REVIEW (
	product_review_no number(7),
	product_no number(7),
	product_order_no number(7),
	product_order_detail_no number(7),
	product_review_writer varchar2(10 char),
	product_review_content varchar2(300 char),
	product_review_star number(2),
	product_review_write_date date,
	CONSTRAINT PRODUCT_REVIEW_PK PRIMARY KEY(PRODUCT_REVIEW_NO, PRODUCT_NO),
	CONSTRAINT PRODUCT_REVIEW_FK FOREIGN KEY(PRODUCT_ORDER_NO,PRODUCT_ORDER_DETAIL_NO) REFERENCES PRODUCT_ORDER_DETAIL(PRODUCT_ORDER_NO,PRODUCT_ORDER_DETAIL_NO)
);

create sequence product_review_seq_product_review_no;

create table CART (
	cart_no number(7),
	member_no number(7),
	cart_total_price number(8),
	CONSTRAINT CART_PK PRIMARY KEY(CART_NO, MEMBER_NO)
);

create sequence cart_seq_cart_no;

create table CART_DETAIL (
	cart_detail_no number(7),
	member_no number(7),
	cart_no number(7),
	product_no number(7),
	cart_detail_count number(2),
	cart_detail_price number(7),
	CONSTRAINT CART_DETAIL_PK PRIMARY KEY(CART_DETAIL_NO, MEMBER_NO, CART_NO),
	CONSTRAINT CART_DETAIL_FK FOREIGN KEY(PRODUCT_NO) REFERENCES PRODUCT(PRODUCT_NO)
);

create sequence cart_detail_seq_cart_detail_no;

create table QNA (
	qna_no number(7),
	product_no number(7),
	qna_writer varchar2(10 char),
	qna_content varchar2(300 char),
	qna_write_date date,
	qna_answer_content varchar2(300 char),
	qna_answer_write_date date,
	CONSTRAINT QNA_PK PRIMARY KEY(QNA_NO),
	CONSTRAINT QNA_FK FOREIGN KEY(PRODUCT_NO) REFERENCES PRODUCT(PRODUCT_NO)
);

create sequence qna_seq_qna_no;

create table CHATROOM(
	chatroom_no number(8),
	member_no number(8),
	chatroom_receiver_no number(8),
CONSTRAINT CHATROOM_PK PRIMARY KEY(CHATROOM_NO),
CONSTRAINT CHATROOM_FK FOREIGN KEY(MEMBER_NO) REFERENCES MEMBER(MEMBER_NO)
);

create sequence chatroom_seq_chatroom_no;

create table MESSAGE (
	message_no number(20),
	message_sender number(8),
	message_receiver number(8),
	message_content varchar2(600 char),
	message_content_type varchar2(10 char),
	message_write_date date,
		CONSTRAINT MESSAGE_PK PRIMARY KEY (MESSAGE_NO)
);

create sequence message_seq_message_no;

create table PUCHING (
	puching_no number(10),
	message_no number(10),
	puching_created_date date,
	puching_status varchar2(6 char),
	puching_promise_date date,
	puching_latitude number(10,7),
	puching_longitude number(10,7),
	puching_apply_date date,
	puching_deadline_date date,
	CONSTRAINT PUCHING_PK PRIMARY KEY(PUCHING_NO),
	CONSTRAINT PUCHING_FK FOREIGN KEY(MESSAGE_NO) REFERENCES MESSAGE(MESSAGE_NO)
);

create sequence puching_seq_puching_no;

create table PUCHING_REVIEW (
	puching_review_no number(10),
	puching_no number(10),
	puching_review_writer number(8),
	puching_review_receiver number(8),
	puching_review_content varchar2(300 char),
	puching_review_star number(2),
	puching_review_write_date date,
	CONSTRAINT PUCHING_REVIEW_PK PRIMARY KEY(PUCHING_REVIEW_NO),
	CONSTRAINT PUCHING_REVIEW_FK FOREIGN KEY(PUCHING_NO) REFERENCES PUCHING(PUCHING_NO) 

);

create sequence puching_review_seq_puching_review_no;

CREATE OR REPLACE FUNCTION radians(deg NUMBER) RETURN NUMBER
IS
BEGIN
  RETURN deg * (3.141592653589793 / 180);
END;
/
CREATE FUNCTION distance(lat1 NUMBER, lon1 NUMBER, lat2 NUMBER, lon2 NUMBER)
RETURN NUMBER
IS
  earth_radius NUMBER := 6371; -- 지구 반지름 (단위: km)
  d_lat NUMBER := RADIANS(lat2 - lat1);
  d_lon NUMBER := RADIANS(lon2 - lon1);
  a NUMBER := POWER(SIN(d_lat / 2), 2) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * POWER(SIN(d_lon / 2), 2);
  c NUMBER := 2 * ATAN2(SQRT(a), SQRT(1 - a));
BEGIN
  RETURN earth_radius * c;
END;

insert into PRODUCT_CATEGORY values (1, 사료/간식);
insert into PRODUCT_CATEGORY values (2, 장난감);
insert into PRODUCT_CATEGORY values (3, 산책용품);
insert into PRODUCT_CATEGORY values (4, 기타);

alter table PRODUCT_REVIEW modify product_review_writer number(8);
alter table QNA modify qna_writer number(8);

05/05
alter table ALARM rename to Notification;
ALTER TABLE NOTIFICATION RENAME COLUMN ALARM_NO TO Notification_no;
ALTER TABLE NOTIFICATION RENAME COLUMN ALARM_CONTENT TO Notification_content;
ALTER TABLE NOTIFICATION RENAME COLUMN ALARM_link TO Notification_link;
ALTER TABLE NOTIFICATION RENAME COLUMN ALARM_READ_ENABLED TO Notification_read_enabled;
alter table NOTIFICATION rename constraint ALARM_PK to NOTIFICATION_PK;
alter table NOTIFICATION rename constraint ALARM_FK to NOTIFICATION_FK;
rename ALARM_SEQ_ALARM_NO to NOTIFICATION_SEQ_NOTIFICATION_NO;

05/09
alter table CART_DETAIL drop constraint CART_DETAIL_PK;
alter table CART_DETAIL drop constraint CART_DETAIL_FK;
alter table CART_DETAIL add constraint CART_DETAIL_FK foreign key (MEMBER_NO, CART_NO) references cart(MEMBER_NO, CART_NO);
alter table CART_DETAIL add constraint CART_DETAIL_FK_PRODUCT_NO foreign key (PRODUCT_NO) references PRODUCT(PRODUCT_NO);
alter table CART_DETAIL add constraint CART_DETAIL_PK primary key (MEMBER_NO, CART_NO, CART_DETAIL_NO);

alter table ADDRESS add default_address_enabled number(1);

5/18
alter table KNOWLEDGE_QUESTION add knowledge_question_enabled number(1,0);

5/23
ALTER TABLE product_order DROP CONSTRAINT PRODUCT_ORDER_FK;
ALTER TABLE PRODUCT_ORDER ADD CONSTRAINT PRODUCT_ORDER_FK FOREIGN KEY (MEMBER_NO) REFERENCES MEMBER (MEMBER_NO);
ALTER TABLE PRODUCT_ORDER ADD PRODUCT_ORDER_ADDRESS VARCHAR2(100 CHAR);
ALTER TABLE FAVORITE DROP CONSTRAINT FAVORITE_FK;
ALTER TABLE FAVORITE ADD CONSTRAINT FAVORITE_FK FOREIGN KEY (PRODUCT_NO) REFERENCES PRODUCT(PRODUCT_NO) on delete CASCADE ;
drop sequence KNOWLEDGE_ANSWER_SEQ_KNOWLEDGE_ANSWER_NO;
drop sequence KNOWLEDGE_QUESTION_SEQ_KNOWLEDGE_QUESTION_NO;
drop sequence BOARD_NOTICE_SEQ_BOARD_NOTICE_NO;
alter table PRODUCT_ORDER drop column ADDRESS_NO;

6/7 
alter table BOARD_NOTICE drop column BOARD_NOTICE_READ_COUNT;
alter table KNOWLEDGE_QUESTION drop column KNOWLEDGE_QUESTION_READ_COUNT;
alter table board drop column board_RECOMME_COUNT;
alter table board drop column board_READ_COUNT;
alter table KNOWLEDGE_ANSWER drop column KNOWLEDGE_ANSWER_RECOMMEND;

6/8
alter table DOGTYPE.BOARD drop primary key;
alter table BOARD add constraint BOARD_PK primary key (BOARD_NO);
insert into TOWN VALUES (0,'','',0,0);

6/9
drop trigger BOARD_INSERT_TRIGGER;

6/20
alter table PRODUCT add PRODUCT_RECOMMEND_ENABLED number(1,0);
alter table PRODUCT modify product_recommend_enabled default 0;

6/22
alter table MEMBER modify member_email varchar2(40 CHAR);