ev-charger-as/backend/init_db.sql

-- ============================================================
-- EV 충전기 AS 관리 시스템 DB 스키마
-- ============================================================

CREATE TABLE IF NOT EXISTS charger_types (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS users (
    id            SERIAL PRIMARY KEY,
    username      VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role          VARCHAR(20) NOT NULL CHECK (role IN ('admin','mechanic','manufacturer')),
    company       VARCHAR(100),
    name          VARCHAR(50) NOT NULL,
    phone         VARCHAR(20),
    email         VARCHAR(100),
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS chargers (
    id               VARCHAR(50) PRIMARY KEY,
    charger_type_id  INT REFERENCES charger_types(id),
    name             VARCHAR(100) NOT NULL,
    station_name     VARCHAR(100) NOT NULL,
    location_detail  TEXT,
    cpo_name         VARCHAR(100),
    installed_at     DATE,
    gps_lat          DOUBLE PRECISION,
    gps_lng          DOUBLE PRECISION,
    is_active        BOOLEAN DEFAULT TRUE,
    created_at       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS reports (
    id           SERIAL PRIMARY KEY,
    charger_id   VARCHAR(50) REFERENCES chargers(id),
    issue_types  TEXT[] NOT NULL,
    issue_detail TEXT,
    error_code   VARCHAR(100),
    occurred_at  TIMESTAMP,
    contact      VARCHAR(20),
    consent      BOOLEAN DEFAULT FALSE,
    gps_lat      DOUBLE PRECISION,
    gps_lng      DOUBLE PRECISION,
    status       VARCHAR(30) DEFAULT 'pending'
                 CHECK (status IN ('pending_approval','pending','in_progress','done','waiting','revisit')),
    reported_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS report_photos (
    id          SERIAL PRIMARY KEY,
    report_id   INT REFERENCES reports(id) ON DELETE CASCADE,
    file_path   VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS repairs (
    id             SERIAL PRIMARY KEY,
    mechanic_id    INT REFERENCES users(id),
    repair_types   TEXT[] NOT NULL,
    description    TEXT NOT NULL,
    started_at     TIMESTAMP NOT NULL,
    completed_at   TIMESTAMP,
    result_status  VARCHAR(20) DEFAULT 'done'
                   CHECK (result_status IN ('done','waiting','revisit'))
);

CREATE TABLE IF NOT EXISTS repair_reports (
    repair_id INT REFERENCES repairs(id) ON DELETE CASCADE,
    report_id INT REFERENCES reports(id) ON DELETE CASCADE,
    PRIMARY KEY (repair_id, report_id)
);

CREATE TABLE IF NOT EXISTS repair_photos (
    id          SERIAL PRIMARY KEY,
    repair_id   INT REFERENCES repairs(id) ON DELETE CASCADE,
    photo_type  VARCHAR(10) DEFAULT 'after' CHECK (photo_type IN ('before','after')),
    file_path   VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS repair_costs (
    id                          SERIAL PRIMARY KEY,
    repair_id                   INT UNIQUE REFERENCES repairs(id) ON DELETE CASCADE,
    root_cause                  TEXT,
    admin_note                  TEXT,
    cost_party_type             VARCHAR(20)
                                CHECK (cost_party_type IN ('cpo','manufacturer','self','user','other')),
    cost_party_manufacturer_id  INT REFERENCES users(id),
    cost_party_custom           VARCHAR(100),
    cost_amount                 INT DEFAULT 0,
    cost_status                 VARCHAR(20) DEFAULT 'pending'
                                CHECK (cost_status IN ('pending','billed','waived','settled')),
    reviewed_by                 INT REFERENCES users(id),
    reviewed_at                 TIMESTAMP
);

CREATE TABLE IF NOT EXISTS improvements (
    id                  SERIAL PRIMARY KEY,
    title               VARCHAR(200) NOT NULL,
    category            VARCHAR(20) NOT NULL
                        CHECK (category IN ('sw','hw','ui','firmware','other')),
    description         TEXT NOT NULL,
    priority            VARCHAR(10) DEFAULT 'normal'
                        CHECK (priority IN ('urgent','high','normal','low')),
    part_name           VARCHAR(100),
    status              VARCHAR(20) DEFAULT 'registered'
                        CHECK (status IN ('registered','reviewing','developing','deployed','done')),
    manufacturer_id     INT REFERENCES users(id),
    created_by          INT REFERENCES users(id),
    sw_deploy_target    DATE,
    sw_deployed_at      DATE,
    manufacturer_memo   TEXT,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS improvement_reports (
    improvement_id  INT REFERENCES improvements(id) ON DELETE CASCADE,
    report_id       INT REFERENCES reports(id) ON DELETE CASCADE,
    PRIMARY KEY (improvement_id, report_id)
);

CREATE TABLE IF NOT EXISTS improvement_attachments (
    id              SERIAL PRIMARY KEY,
    improvement_id  INT REFERENCES improvements(id) ON DELETE CASCADE,
    file_path       VARCHAR(255) NOT NULL,
    file_name       VARCHAR(255),
    uploaded_at     TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS improvement_logs (
    id              SERIAL PRIMARY KEY,
    improvement_id  INT REFERENCES improvements(id) ON DELETE CASCADE,
    changed_by      INT REFERENCES users(id),
    old_status      VARCHAR(20),
    new_status      VARCHAR(20),
    memo            TEXT,
    changed_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS system_settings (
    key        VARCHAR(100) PRIMARY KEY,
    value      TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- 기본 데이터
-- ============================================================

INSERT INTO charger_types (name, description) VALUES
    ('완속충전기', 'AC 7kW 이하'),
    ('급속충전기', 'DC 50kW'),
    ('초급속충전기', 'DC 100kW 이상')
ON CONFLICT DO NOTHING;

INSERT INTO system_settings (key, value) VALUES
    ('report_visibility_policy', 'immediate')
ON CONFLICT DO NOTHING;

-- 초기 관리자 계정: admin / admin1234
INSERT INTO users (username, password_hash, role, name, is_active) VALUES
    ('admin', '$2b$12$ocMnUviG6lYZ4BP4Ut00KumPg/L73b82eJCEfrXUmwfFcFy3zfWDO', 'admin', '관리자', true)
ON CONFLICT DO NOTHING;