-- EP33: Flyway로 스키마를 코드(버전 관리)로 전환.
-- 지금까지 ddl-auto=update로 생성되어온 member/company/job/application 테이블을
-- 동일한 구조로 다시 정의한다. 앞으로의 스키마 변경은 V2, V3, ... 새 파일로만 추가한다.

CREATE TABLE company (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(100),
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE member (
    id          BIGSERIAL PRIMARY KEY,
    email       VARCHAR(100) NOT NULL UNIQUE,
    password    VARCHAR(100) NOT NULL,
    role        VARCHAR(20) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE job (
    id            BIGSERIAL PRIMARY KEY,
    company_id    BIGINT NOT NULL REFERENCES company(id),
    registered_by BIGINT NOT NULL,
    title         VARCHAR(200) NOT NULL,
    description   TEXT,
    salary        INTEGER,
    status        VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    deadline      DATE,
    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE application (
    id          BIGSERIAL PRIMARY KEY,
    member_id   BIGINT NOT NULL REFERENCES member(id),
    job_id      BIGINT NOT NULL REFERENCES job(id),
    status      VARCHAR(20) NOT NULL DEFAULT 'APPLIED',
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_application_member_job UNIQUE (member_id, job_id)
);

CREATE INDEX idx_job_company_id ON job(company_id);
CREATE INDEX idx_job_status ON job(status);
CREATE INDEX idx_application_member_id ON application(member_id);
