-- 1) 회원 테이블
CREATE TABLE member (
  mno          INT            NOT NULL AUTO_INCREMENT,
  name         VARCHAR(45)    NOT NULL,
  email        VARCHAR(45)    NOT NULL UNIQUE,
  password     VARCHAR(100)   NOT NULL,
  PRIMARY KEY (mno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) 게시판 카테고리
CREATE TABLE community_category (
  category_id   VARCHAR(20)   NOT NULL,
  category_name VARCHAR(50)   NOT NULL,
  PRIMARY KEY (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) 게시글
CREATE TABLE community_post (
  post_id      INT            NOT NULL AUTO_INCREMENT,
  category_id  VARCHAR(20)    NOT NULL,
  author_id    INT            NOT NULL,
  title        VARCHAR(200)   NOT NULL,
  content      TEXT           NOT NULL,
  created_at   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  views        INT            NOT NULL DEFAULT 0,
  PRIMARY KEY (post_id),
  INDEX idx_post_category (category_id),
  INDEX idx_post_author   (author_id),
  FOREIGN KEY (category_id)
    REFERENCES community_category(category_id),
  FOREIGN KEY (author_id)
    REFERENCES member(mno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) 댓글
CREATE TABLE community_comment (
  comment_id   INT            NOT NULL AUTO_INCREMENT,
  post_id      INT            NOT NULL,
  author_id    INT            NOT NULL,
  content      TEXT           NOT NULL,
  created_at   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (comment_id),
  INDEX idx_cmt_post   (post_id),
  INDEX idx_cmt_author (author_id),
  FOREIGN KEY (post_id)
    REFERENCES community_post(post_id),
  FOREIGN KEY (author_id)
    REFERENCES member(mno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) 즐겨찾기
CREATE TABLE member_favorite (
  id        BIGINT        NOT NULL AUTO_INCREMENT,
  mno       INT           NOT NULL,
  apt_seq   VARCHAR(20)   NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_fav_mno     (mno),
  INDEX idx_fav_apt_seq (apt_seq),
  FOREIGN KEY (mno)
    REFERENCES member(mno),
  FOREIGN KEY (apt_seq)
    REFERENCES house_info(apt_seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6) 아파트 기본 정보
CREATE TABLE house_info (
  apt_seq         VARCHAR(20)   NOT NULL,
  sgg_cd          VARCHAR(5)    NOT NULL,
  umd_cd          VARCHAR(5)    NOT NULL,
  umd_nm          VARCHAR(20)   NOT NULL,
  jibun           VARCHAR(10)   NULL,
  road_nm_sgg_cd  VARCHAR(5)    NULL,
  road_nm         VARCHAR(20)   NULL,
  road_nm_bonbun  VARCHAR(10)   NULL,
  road_nm_bubun   VARCHAR(10)   NULL,
  apt_nm          VARCHAR(40)   NOT NULL,
  build_year      INT           NULL,
  latitude        VARCHAR(45)   NULL,
  longitude       VARCHAR(45)   NULL,
  PRIMARY KEY (apt_seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7) 거래 내역
CREATE TABLE house_deal (
  deal_id       BIGINT        NOT NULL AUTO_INCREMENT,
  apt_seq       VARCHAR(20)   NOT NULL,
  listing_name  VARCHAR(100)  NULL,
  trade_type    VARCHAR(10)   NULL,
  price_now     BIGINT        NULL,
  spec          VARCHAR(100)  NULL,
  description   TEXT          NULL,
  confirmed_at  DATE          NULL,
  deposit       BIGINT        NULL,
  monthly_rent  BIGINT        NULL,
  PRIMARY KEY (deal_id),
  INDEX idx_deal_apt (apt_seq),
  FOREIGN KEY (apt_seq)
    REFERENCES house_info(apt_seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8) 매물 이미지
CREATE TABLE house_image (
  id          INT            NOT NULL AUTO_INCREMENT,
  apt_seq     VARCHAR(20)    NOT NULL,
  img_path    VARCHAR(255)   NOT NULL,
  description VARCHAR(255)   NULL,
  PRIMARY KEY (id),
  INDEX idx_img_apt (apt_seq),
  FOREIGN KEY (apt_seq)
    REFERENCES house_info(apt_seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9) 스코어링 결과
CREATE TABLE house_score (
  apt_seq      VARCHAR(20)   NOT NULL,
  price_now    INT           NULL,
  price_1y_ago INT           NULL,
  score        DOUBLE        NULL,
  last_date    DATETIME      NULL,
  img_path     VARCHAR(255)  NULL,
  PRIMARY KEY (apt_seq),
  FOREIGN KEY (apt_seq)
    REFERENCES house_info(apt_seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10) 매물 상세 속성
CREATE TABLE house_detail (
  apt_seq           VARCHAR(20)   NOT NULL,
  area_min          DECIMAL(8,2)  NULL,
  area_max          DECIMAL(8,2)  NULL,
  trade_price_min   BIGINT        NULL,
  jeonse_price_max  BIGINT        NULL,
  last_trade_date   VARCHAR(10)   NULL,
  school_id         INT           NULL,
  PRIMARY KEY (apt_seq),
  INDEX idx_det_school (school_id),
  FOREIGN KEY (apt_seq)
    REFERENCES house_info(apt_seq),
  FOREIGN KEY (school_id)
    REFERENCES school_detail(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11) 학교 상세 정보
CREATE TABLE school_detail (
  id             INT            NOT NULL AUTO_INCREMENT,
  school_name    VARCHAR(100)   NOT NULL,
  school_type    VARCHAR(50)    NULL,
  address        VARCHAR(255)   NULL,
  phone          VARCHAR(50)    NULL,
  established    VARCHAR(100)   NULL,
  office         VARCHAR(100)   NULL,
  teacher_count  VARCHAR(50)    NULL,
  student_count  VARCHAR(50)    NULL,
  homepage       VARCHAR(255)   NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12) 매물-학교 매핑
CREATE TABLE house_school (
  apt_seq           VARCHAR(20)   NOT NULL,
  school_id         INT            NOT NULL,
  district          VARCHAR(100)   NULL,
  assignment_detail VARCHAR(100)   NULL,
  distance          VARCHAR(50)    NULL,
  PRIMARY KEY (apt_seq, school_id),
  INDEX idx_hs_school (school_id),
  FOREIGN KEY (apt_seq)
    REFERENCES house_info(apt_seq),
  FOREIGN KEY (school_id)
    REFERENCES school_detail(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13) 행정동 코드
CREATE TABLE dong_code (
  dong_code   CHAR(10)      NOT NULL,
  sido_name   VARCHAR(30)   NULL,
  gugun_name  VARCHAR(30)   NULL,
  dong_name   VARCHAR(30)   NULL,
  PRIMARY KEY (dong_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 14) 부동산 뉴스
CREATE TABLE real_estate_news (
  id            INT            NOT NULL AUTO_INCREMENT,
  title         VARCHAR(500)   NULL,
  originallink  VARCHAR(500)   NULL,
  naverlink     VARCHAR(500)   NULL,
  description   TEXT           NULL,
  pub_date      DATETIME       NULL,
  crawled_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;