from __future__ import annotations

import logging
from app.core.postgres.erp_client import ErpDatabaseClient

logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")
logger = logging.getLogger("BoQAllocationMigration")

DDL_SQL = """
-- 1. Bảng Định Mức Vật Tư Dự Toán (BoQ Material Quotas)
CREATE TABLE IF NOT EXISTS erp_project_boq_materials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    wbs_id UUID REFERENCES erp_project_wbs(id) ON DELETE SET NULL,
    material_code VARCHAR(100) NOT NULL,
    material_name VARCHAR(255) NOT NULL,
    material_group VARCHAR(100) NOT NULL, -- CEMENT, STEEL, SAND, STONE, BRICK, ROOFING, TIMBER, PIPE, GEOTEXTILE, OTHER
    unit VARCHAR(50) NOT NULL,
    boq_quantity NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    unit_price_boq_vnd NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    total_budget_boq_vnd NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    invoiced_quantity NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    invoiced_amount_vnd NUMERIC(18, 4) NOT NULL DEFAULT 0.0000,
    remaining_quantity NUMERIC(18, 4) GENERATED ALWAYS AS (GREATEST(0, boq_quantity - invoiced_quantity)) STORED,
    allowed_loss_percent NUMERIC(5, 2) NOT NULL DEFAULT 3.00,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_project_material UNIQUE (project_id, material_code)
);

-- 2. Bảng Nhật Ký Phân Bổ Dòng Hóa Đơn Trực Tiếp Vào Dự Án (Invoice Direct Allocations)
CREATE TABLE IF NOT EXISTS erp_invoice_item_allocations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES erp_invoices(id) ON DELETE CASCADE,
    invoice_item_id UUID NOT NULL REFERENCES erp_invoice_items(id) ON DELETE CASCADE,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE RESTRICT,
    boq_material_id UUID REFERENCES erp_project_boq_materials(id) ON DELETE SET NULL,
    allocated_quantity NUMERIC(18, 4) NOT NULL,
    allocated_amount_before_vat_vnd NUMERIC(18, 4) NOT NULL,
    allocated_vat_amount_vnd NUMERIC(18, 4) NOT NULL,
    allocated_total_amount_vnd NUMERIC(18, 4) NOT NULL,
    allocation_method VARCHAR(50) NOT NULL DEFAULT 'boq_auto', -- 'boq_auto', 'proportional_split', 'manual_override'
    debit_account VARCHAR(50) NOT NULL DEFAULT '621',
    credit_account VARCHAR(50) NOT NULL DEFAULT '331',
    confidence_score NUMERIC(5, 2) NOT NULL DEFAULT 100.00,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_boq_materials_proj ON erp_project_boq_materials(project_id, material_group);
CREATE INDEX IF NOT EXISTS idx_allocations_inv ON erp_invoice_item_allocations(invoice_id, project_id);
CREATE INDEX IF NOT EXISTS idx_allocations_proj ON erp_invoice_item_allocations(project_id);
"""


def run_migration():
    db = ErpDatabaseClient()
    logger.info("Bắt đầu khởi tạo cấu trúc bảng BoQ Material Quotas & Invoice Allocations...")
    with db.get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(DDL_SQL)
        conn.commit()
    logger.info("Khởi tạo cấu trúc bảng thành công 100% trong PostgreSQL!")


if __name__ == "__main__":
    run_migration()