CREATE DATABASE IF NOT EXISTS auction_ax DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE auction_ax;

CREATE TABLE IF NOT EXISTS auction_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    case_no VARCHAR(50) NOT NULL,
    court VARCHAR(50) NOT NULL,
    item_no INT DEFAULT 1,
    property_type VARCHAR(30),
    address VARCHAR(300),
    region_code VARCHAR(10),
    appraisal_price BIGINT,
    min_bid_price BIGINT,
    fail_count INT DEFAULT 0,
    sale_date DATE,
    area_land DECIMAL(10,2),
    area_building DECIMAL(10,2),
    note VARCHAR(500),
    photo_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'active',
    final_bid_price BIGINT NULL,
    first_crawled_at DATETIME,
    last_updated_at DATETIME,
    UNIQUE KEY uk_case (case_no, court, item_no),
    INDEX idx_sale_date (sale_date),
    INDEX idx_region (region_code),
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS item_documents (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    doc_type VARCHAR(30),
    raw_text MEDIUMTEXT,
    crawled_at DATETIME,
    FOREIGN KEY (item_id) REFERENCES auction_items(id) ON DELETE CASCADE,
    INDEX idx_item (item_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS market_prices (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    region_code VARCHAR(10),
    property_type VARCHAR(30),
    complex_name VARCHAR(100),
    area DECIMAL(10,2),
    trade_price BIGINT,
    trade_date DATE,
    INDEX idx_region_type (region_code, property_type, trade_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS item_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    score_date DATE,
    predicted_bid_price BIGINT,
    market_price_est BIGINT,
    market_confidence CHAR(1) DEFAULT 'C',
    discount_rate DECIMAL(6,4),
    competition_score DECIMAL(5,2),
    rank_today INT,
    FOREIGN KEY (item_id) REFERENCES auction_items(id) ON DELETE CASCADE,
    UNIQUE KEY uk_item_date (item_id, score_date),
    INDEX idx_date_rank (score_date, rank_today)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS risk_analysis (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    analyzed_at DATETIME,
    risk_level VARCHAR(10),
    risk_json JSON,
    summary_text TEXT,
    llm_model VARCHAR(50),
    FOREIGN KEY (item_id) REFERENCES auction_items(id) ON DELETE CASCADE,
    INDEX idx_item (item_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS roi_analysis (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NOT NULL,
    calc_date DATE,
    scenario VARCHAR(20),
    est_total_cost BIGINT,
    est_exit_price BIGINT,
    est_profit BIGINT,
    roi_pct DECIMAL(7,2),
    detail_json JSON,
    FOREIGN KEY (item_id) REFERENCES auction_items(id) ON DELETE CASCADE,
    UNIQUE KEY uk_item_date_sc (item_id, calc_date, scenario)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS report_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_date DATE,
    item_ids JSON,
    pdf_path VARCHAR(300),
    sent_at DATETIME,
    send_status VARCHAR(20),
    error_msg TEXT,
    UNIQUE KEY uk_date (report_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pipeline_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    run_date DATE,
    stage VARCHAR(30),
    status VARCHAR(20),
    items_processed INT DEFAULT 0,
    started_at DATETIME,
    finished_at DATETIME,
    error_msg TEXT,
    INDEX idx_date_stage (run_date, stage)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crawl_failures (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    case_no VARCHAR(50),
    court VARCHAR(50),
    reason VARCHAR(300),
    failed_at DATETIME,
    resolved TINYINT DEFAULT 0
) ENGINE=InnoDB;
