-- V002: 누락된 테이블 생성
-- booking_requests, mail_account_file, mail_template_file, open_api_proxy, work_history

CREATE TABLE IF NOT EXISTS booking_requests (
    id               SERIAL PRIMARY KEY,
    customer_name    VARCHAR(255),
    customer_phone   VARCHAR(100),
    pickup_location  TEXT,
    dropoff_location TEXT,
    scheduled_time   TIMESTAMP,
    vehicle_type     VARCHAR(100),
    cargo_type       VARCHAR(100),
    weight           VARCHAR(50),
    status           VARCHAR(50)  DEFAULT 'pending',
    priority         VARCHAR(50)  DEFAULT 'normal',
    notes            TEXT,
    estimated_cost   VARCHAR(50),
    rejection_reason TEXT,
    accepted_at      TIMESTAMP,
    rejected_at      TIMESTAMP,
    completed_at     TIMESTAMP,
    created_at       TIMESTAMP    DEFAULT NOW(),
    updated_at       TIMESTAMP    DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS mail_account_file (
    id            SERIAL PRIMARY KEY,
    company_code  VARCHAR(50),
    name          VARCHAR(255),
    email         VARCHAR(255),
    smtp_host     VARCHAR(255),
    smtp_port     INTEGER,
    smtp_secure   BOOLEAN,
    smtp_username VARCHAR(255),
    smtp_password VARCHAR(255),
    daily_limit   INTEGER,
    status        VARCHAR(50)  DEFAULT 'active',
    created_at    TIMESTAMP    DEFAULT NOW(),
    updated_at    TIMESTAMP    DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS mail_template_file (
    id               SERIAL PRIMARY KEY,
    company_code     VARCHAR(50),
    name             VARCHAR(255),
    subject          VARCHAR(500),
    components       JSONB,
    query_config     JSONB,
    recipient_config JSONB,
    category         VARCHAR(100),
    created_at       TIMESTAMP DEFAULT NOW(),
    updated_at       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS open_api_proxy (
    id           SERIAL PRIMARY KEY,
    company_code VARCHAR(50),
    name         VARCHAR(255),
    status       VARCHAR(50)  DEFAULT 'active',
    created_at   TIMESTAMP    DEFAULT NOW(),
    updated_at   TIMESTAMP    DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS work_history (
    id               SERIAL PRIMARY KEY,
    work_number      VARCHAR(100),
    work_date        DATE,
    work_type        VARCHAR(50),
    vehicle_number   VARCHAR(100),
    driver_name      VARCHAR(255),
    origin           VARCHAR(255),
    destination      VARCHAR(255),
    cargo_name       VARCHAR(255),
    cargo_weight     NUMERIC,
    cargo_unit       VARCHAR(50),
    distance         NUMERIC,
    distance_unit    VARCHAR(50),
    status           VARCHAR(50)  DEFAULT 'pending',
    scheduled_time   TIMESTAMP,
    estimated_arrival TIMESTAMP,
    actual_arrival   TIMESTAMP,
    is_on_time       BOOLEAN,
    notes            TEXT,
    created_by       VARCHAR(255),
    created_at       TIMESTAMP    DEFAULT NOW(),
    updated_at       TIMESTAMP    DEFAULT NOW(),
    deleted_at       TIMESTAMP
);
