#!/usr/bin/env python3
"""Generate the deterministic, internally reconciled fresh-install demo seed.

Run from the repository root: python3 scripts/generate-seed.py
This script never connects to a database. Relative SQL dates use Vietnam time.
"""
from collections import defaultdict
from pathlib import Path

OUT = Path('database/seed.sql')
lines = ["-- StockFlow fresh-install demo data. Run after database/schema.sql.",
         "-- Dates use the Vietnam calendar. Set @seed_date before import to replay a fixed day.",
         "-- IDs below are deterministic for a freshly created schema; this seed is not idempotent.",
         "-- Existing demo login passwords: admin / Admin@123; warehouse01 and warehouse02 / Staff@123.",
         "-- Schema.sql owns every table and view; this file supplies demo records only.",
         "SET NAMES utf8mb4;",
         "SET time_zone = '+07:00';",
         "SET @seed_date = COALESCE(@seed_date, CURRENT_DATE());",
         "START TRANSACTION;", ""]
TODAY = "@seed_date"
def date(offset): return f"DATE_ADD({TODAY}, INTERVAL {offset} DAY)"
def dt(offset, hour=9): return f"TIMESTAMP({date(offset)}, '{hour:02}:00:00')"
class Raw(str): pass
def q(value):
    if isinstance(value, Raw): return str(value)
    if value is None: return 'NULL'
    if isinstance(value, (int, float)): return str(value)
    return "'" + str(value).replace('\\', '\\\\').replace("'", "''") + "'"
def insert(table, cols, rows):
    if not rows: return
    lines.append(f"INSERT INTO {table} ({', '.join(cols)}) VALUES")
    lines.append(',\n'.join('(' + ', '.join(q(v) for v in row) + ')' for row in rows) + ';\n')

categories = [('CAT-COMP','Linh kiện máy tính','Thiết bị và phụ kiện máy tính'),('CAT-NET','Thiết bị mạng','Kết nối mạng'),('CAT-ELEC','Thiết bị điện','Điện dân dụng'),('CAT-STORE','Lưu trữ','Ổ đĩa và lưu trữ'),('CAT-OFFICE','Văn phòng','Thiết bị văn phòng'),('CAT-SEC','An ninh','Camera và giám sát'),('CAT-CABLE','Dây cáp','Cáp tín hiệu và nguồn'),('CAT-TOOLS','Công cụ','Dụng cụ bảo trì')]
insert('categories',['category_code','category_name','description'],categories)
insert('units',['unit_code','unit_name','symbol','description'],[('PCS','Cái','cái','Đơn vị chiếc'),('BOX','Hộp','hộp','Đơn vị hộp'),('ROLL','Cuộn','cuộn','Đơn vị cuộn'),('SET','Bộ','bộ','Đơn vị bộ'),('MTR','Mét','m','Đơn vị chiều dài'),('PAIR','Đôi','đôi','Đơn vị theo cặp'),('PACK','Gói','gói','Đơn vị theo gói'),('CASE','Thùng','thùng','Đơn vị theo thùng')])
insert('warehouses',['warehouse_code','warehouse_name','address','description'],[('KHO01','Kho trung tâm','12 Nguyễn Trãi, Quận 1, TP.HCM','Kho bán hàng chính'),('KHO02','Kho miền Đông','88 Điện Biên Phủ, Bình Thạnh, TP.HCM','Kho giao hàng khu vực'),('KHO03','Kho cách ly','20 Lê Lợi, Quận 1, TP.HCM','Hàng trả và chờ xử lý'),('KHO04','Kho miền Tây','15 Cách Mạng Tháng Tám, Cần Thơ','Kho giao hàng khu vực'),('KHO05','Kho dự án','31 Trần Phú, Đà Nẵng','Kho dành cho dự án')])
insert('users',['username','password_hash','full_name','email','role','status'],[('admin','$2b$10$Rzp7bvB1osDykg4gdeFB5OmAfYbBmzoypoQRq4p7Ku3GqrHKbnUo2','Quản trị viên','admin@stockflow.local','ADMIN','ACTIVE'),('warehouse01','$2b$10$jJFkiRdYbQqNePHKbqGVXeJGuh63BSjfm4EQCcO1cXAyHJH4rJD9q','Nguyễn Văn Kho','warehouse01@stockflow.local','WAREHOUSE_STAFF','ACTIVE'),('warehouse02','$2b$10$VLkq8Ox1qzdbB6sPccCPLe2Wm4jGt4IFt/m5bR64VF5SILXsbAE3q','Trần Thị Kho','warehouse02@stockflow.local','WAREHOUSE_STAFF','ACTIVE'),('manager01','$2b$10$Rzp7bvB1osDykg4gdeFB5OmAfYbBmzoypoQRq4p7Ku3GqrHKbnUo2','Lê Minh Quản','manager01@stockflow.local','ADMIN','ACTIVE'),('warehouse03','$2b$10$jJFkiRdYbQqNePHKbqGVXeJGuh63BSjfm4EQCcO1cXAyHJH4rJD9q','Phạm Thu Hà','warehouse03@stockflow.local','WAREHOUSE_STAFF','ACTIVE'),('warehouse_old','$2b$10$VLkq8Ox1qzdbB6sPccCPLe2Wm4jGt4IFt/m5bR64VF5SILXsbAE3q','Nhân viên cũ','warehouse_old@stockflow.local','WAREHOUSE_STAFF','INACTIVE')])
suppliers=[('NCC001','Công ty Công nghệ Sao Việt','0301000001'),('NCC002','Nhà phân phối Phú Quý','0102000002'),('NCC003','Thiết bị điện Đông Á','3703000003'),('NCC004','Công ty Mạng Toàn Cầu','0305000004'),('NCC005','Điện tử Đại Nam','0406000005'),('NCC006','Công ty Văn phòng Hòa Bình','1807000006')]
customers=[('KH001','Công ty Minh Long','0304000001'),('KH002','Công ty An Bình','0304000002'),('KH003','Đại lý Hoàng Gia','0304000003'),('KH004','Công ty Tân Phát','0304000004'),('KH005','Siêu thị Công nghệ Miền Tây','1804000005'),('KH006','Trường Đại học Hải Đăng','0404000006'),('KH007','Cửa hàng Nhật Minh','0304000007')]
insert('suppliers',['supplier_code','supplier_name','tax_code','contact_name','phone','email','address'],[(code,name,tax,'Bộ phận kinh doanh',f'028{1000000+i:07d}',f'sales{i}@supplier.local', ['TP.HCM','Hà Nội','Bình Dương','Đà Nẵng','Cần Thơ','Hải Phòng'][i-1]) for i,(code,name,tax) in enumerate(suppliers,1)])
insert('customers',['customer_code','customer_name','tax_code','contact_name','phone','email','address'],[(code,name,tax,'Bộ phận mua hàng',f'090{1000000+i:07d}',f'contact{i}@customer.local',['TP.HCM','Đồng Nai','Bình Dương','Long An','Cần Thơ','Đà Nẵng','TP.HCM'][i-1]) for i,(code,name,tax) in enumerate(customers,1)])
products=[
('Chuột Logitech G102',1,1,12,300000,350000),('Bàn phím Logitech K120',1,1,10,200000,240000),('RAM Kingston 16GB',1,1,10,800000,900000),('SSD Samsung 500GB',4,1,8,1000000,1150000),('Bộ phát WiFi TP-Link',2,1,7,600000,750000),('Dây điện Cadivi 4.0',3,3,20,45000,55000),('Router TP-Link AX23',2,1,6,850000,1050000),('Switch TP-Link 8 cổng',2,1,6,420000,520000),('Ổ cắm điện 5 mét',3,1,12,120000,150000),('Hộp đầu nối điện',3,2,10,80000,100000),
('Chuột Logitech M331',1,1,10,280000,340000),('Bàn phím cơ Akko 3087',1,1,5,1150000,1390000),('RAM Corsair 32GB',1,1,6,1550000,1850000),('SSD WD Blue 1TB',4,1,5,1650000,1990000),('Ổ cứng HDD Seagate 2TB',4,1,5,1350000,1690000),('USB SanDisk 64GB',4,1,15,180000,230000),('Thẻ nhớ Samsung 128GB',4,1,12,290000,360000),('Router MikroTik hEX',2,1,5,1250000,1590000),('Switch Cisco 24 cổng',2,1,4,2800000,3400000),('Bộ mở rộng WiFi',2,1,8,390000,490000),
('Camera IP Hikvision 2MP',6,1,6,890000,1120000),('Đầu ghi hình 8 kênh',6,1,4,2350000,2900000),('Ổ cứng camera 4TB',6,1,4,2450000,3000000),('Máy in Canon LBP2900',5,1,4,4100000,4800000),('Mực in Canon 303',5,2,8,950000,1150000),('Máy quét Epson V39',5,1,3,2550000,3100000),('Bộ lưu điện APC 650VA',3,1,5,1350000,1650000),('Dây mạng Cat6',2,3,15,750000,950000),('Ổ cắm chống sét',3,1,8,220000,280000),('Bộ phụ kiện vệ sinh máy tính',5,4,10,90000,125000),('Cáp HDMI 2 mét',1,1,12,125000,165000),('Adapter USB-C đa cổng',1,1,6,490000,620000),('Bộ chuột bàn phím không dây',1,4,8,520000,650000),('Pin dự phòng 10000mAh',3,1,8,410000,510000),('Camera WiFi trong nhà',6,1,6,730000,920000),('Dây điện Cadivi 2.5',3,3,15,350000,430000)]
insert('products',['product_code','product_name','category_id','unit_id','minimum_stock','import_price','export_price','description'],[(f'SP{i:03}',name,cat,unit,minq,ip,ep,f'Hàng mẫu {name}') for i,(name,cat,unit,minq,ip,ep) in enumerate(products,1)])

# A document is (code,type,warehouse,partner,source,status,day,[(product,quantity)]).
imports=[
('PN000001','PURCHASE',1,1,None,'CONFIRMED',-55,[(1,70),(2,45),(11,30),(12,12)]),
('PN000002','PURCHASE',1,2,None,'CONFIRMED',-51,[(3,35),(4,25),(13,18),(14,16)]),
('PN000003','PURCHASE',2,3,None,'CONFIRMED',-47,[(6,75),(9,40),(10,35),(27,20)]),
('PN000004','PURCHASE',1,4,None,'CONFIRMED',-43,[(5,30),(7,22),(8,24),(18,18),(19,10),(20,20),(28,30)]),
('PN000005','PURCHASE',4,5,None,'CONFIRMED',-39,[(21,20),(22,12),(23,12),(35,20)]),
('PN000006','PURCHASE',5,6,None,'CONFIRMED',-34,[(24,12),(25,24),(26,10),(30,40)]),
('PN000007','PURCHASE',1,1,None,'CONFIRMED',-29,[(15,20),(16,55),(17,45),(31,40),(32,20)]),
('PN000008','PURCHASE',2,2,None,'CONFIRMED',-25,[(29,35),(33,22),(34,25),(36,40)]),
('PNTP000001','FINISHED_GOODS',1,None,None,'CONFIRMED',-22,[(5,12),(33,8)]),
('PNKH000001','OTHER',4,None,None,'CONFIRMED',-19,[(8,8),(16,12)]),
('PN000009','PURCHASE',5,4,None,'DRAFT',-4,[(7,10),(20,15)]),
('PN000010','PURCHASE',2,3,None,'CANCELLED',-14,[(9,8)]),
('PNTR000001','CUSTOMER_RETURN',3,1,1,'CONFIRMED',-8,[(1,2)]),
('PNTR000002','CUSTOMER_RETURN',3,3,3,'DRAFT',-2,[(21,1)]),
]
exports=[
('PX000001','SALE',1,1,None,'CONFIRMED',-20,[(1,12),(2,6),(11,5)]),
('PX000002','SALE',1,2,None,'CONFIRMED',-18,[(3,7),(4,4),(13,2)]),
('PX000003','SALE',4,3,None,'CONFIRMED',-16,[(21,5),(22,2),(35,4)]),
('PX000004','SALE',2,4,None,'CONFIRMED',-14,[(6,10),(9,6),(10,5)]),
('PX000005','SALE',1,5,None,'CONFIRMED',-11,[(5,6),(7,3),(8,4)]),
('PX000006','SALE',5,6,None,'CONFIRMED',-9,[(24,2),(25,5),(26,2)]),
('PX000007','SALE',2,7,None,'CONFIRMED',-7,[(29,5),(33,3),(34,4)]),
('PX000008','SALE',1,1,None,'CONFIRMED',-5,[(15,3),(16,8),(17,5)]),
('PXTR000001','SUPPLIER_RETURN',1,2,2,'CONFIRMED',-6,[(3,2),(4,1)]),
('PXNV000001','INTERNAL_USE',5,None,None,'CONFIRMED',-5,[(30,3)]),
('PXHU000001','DISPOSAL',2,None,None,'CONFIRMED',-3,[(9,2)]),
('PXKH000001','OTHER',1,None,None,'CONFIRMED',-2,[(31,1)]),
('PX000009','SALE',1,3,None,'DRAFT',-1,[(1,3),(2,2)]),
('PX000010','SALE',2,5,None,'CANCELLED',-12,[(29,2)]),
('PXTR000002','SUPPLIER_RETURN',1,1,1,'DRAFT',-1,[(1,1)]),
]
# Return import source IDs refer to confirmed sale exports. Insert the ordinary imports
# first, exports next, and the customer returns last; explicit IDs keep linkage stable.
import_rows=[]; detail_rows=[]
for i,(code,typ,wh,partner,source,status,day,items) in enumerate(imports,1):
    import_rows.append((i,code,typ,wh,partner if typ=='PURCHASE' else None,partner if typ=='CUSTOMER_RETURN' else None,source,2,'Nhân viên giao hàng',f'CT-{i:04}' if typ=='PURCHASE' else None,('Đối tác' if partner else 'Bộ phận nội bộ'),Raw(date(day)),f'Dữ liệu mẫu: {typ}',status))
    for p,qty in items: detail_rows.append((i,p,qty,qty,products[p-1][4],products[p-1][4],None))
import_columns=['import_id','import_code','import_type','warehouse_id','supplier_id','customer_id','source_export_id','created_by','delivery_person','invoice_number','source_name','import_date','reason','status']
insert('import_receipts',import_columns,import_rows[:12])
export_rows=[]; export_detail=[]
for i,(code,typ,wh,partner,source,status,day,items) in enumerate(exports,1):
    export_rows.append((i,code,typ,wh,partner if typ=='SALE' else None,partner if typ=='SUPPLIER_RETURN' else None,source,3,'Người nhận hàng',Raw(date(day)),f'Dữ liệu mẫu: {typ}',status))
    for p,qty in items: export_detail.append((i,p,qty,qty,products[p-1][5] if typ=='SALE' else products[p-1][4],products[p-1][5],None))
insert('export_receipts',['export_id','export_code','export_type','warehouse_id','customer_id','supplier_id','source_import_id','created_by','receiver_name','export_date','reason','status'],export_rows)
insert('export_details',['export_id','product_id','requested_quantity','actual_quantity','unit_price','export_price','note'],export_detail)
insert('import_receipts',import_columns,import_rows[12:])
insert('import_details',['import_id','product_id','document_quantity','actual_quantity','unit_price','import_price','note'],detail_rows)

transfers=[('CK000001',1,2,'CONFIRMED',-13,[(1,8),(2,5),(5,6)]),('CK000002',1,5,'CONFIRMED',-10,[(3,4),(16,10),(31,6)]),('CK000003',2,4,'CONFIRMED',-6,[(6,8),(29,5)]),('CK000004',4,1,'DRAFT',-1,[(21,2)]),('CK000005',5,2,'CANCELLED',-4,[(25,2)])]
insert('transfer_receipts',['transfer_code','from_warehouse_id','to_warehouse_id','created_by','carrier_name','transfer_date','reason','status'],[(code,a,b,2,'Nguyễn Văn T',Raw(date(day)),'Điều phối hàng mẫu',status) for code,a,b,status,day,items in transfers])
insert('transfer_details',['transfer_id','product_id','requested_quantity','actual_quantity','unit_price'],[(i,p,qty,qty,products[p-1][4]) for i,(*_,items) in enumerate(transfers,1) for p,qty in items])

stock=defaultdict(int); damaged=defaultdict(int); obsolete=defaultdict(int); movements=[]
def move(wh,p,typ,ref,rid,delta,day,note):
    stream = damaged if typ=='DAMAGE_ADJUSTMENT' else obsolete if typ=='OBSOLETE_ADJUSTMENT' else stock
    before=stream[wh,p]; after=before+delta
    assert after >= 0, (wh,p,typ,ref,rid,delta)
    stream[wh,p]=after
    movements.append((wh,p,2,typ,ref,rid,before,delta,after,Raw(dt(day)),note))
checks=[('KK000001',1,'CONFIRMED',-4,[(1,-1,1,0),(2,0,0,0),(3,0,0,0)]),('KK000002',2,'CONFIRMED',-2,[(6,0,2,0),(9,0,0,1),(29,1,0,0)]),('KK000003',4,'DRAFT',-1,[(21,0,0,0),(35,0,0,0)]),('KK000004',5,'CANCELLED',-7,[(24,0,0,0)])]
check_rows=[]; check_detail=[]
adjustments=[('DC000001',1,1,'CONFIRMED',-3,[(1,0,0,0),(2,0,0,0)]),('DC000002',2,None,'CONFIRMED',-1,[(5,3,1,0),(9,-1,0,0)]),('DC000003',5,None,'DRAFT',0,[(25,1,0,0)]),('DC000004',4,None,'CANCELLED',-2,[(21,-1,0,0)])]
adjust_rows=[];adjust_detail=[]
events=[]
for i,row in enumerate(imports,1): events.append((row[6],0,'import',i,row))
for i,row in enumerate(exports,1): events.append((row[6],1,'export',i,row))
for i,row in enumerate(transfers,1): events.append((row[4],2,'transfer',i,row))
for i,row in enumerate(checks,1): events.append((row[3],3,'check',i,row))
for i,row in enumerate(adjustments,1): events.append((row[4],4,'adjustment',i,row))
for day,_,kind,i,row in sorted(events):
    if kind=='import':
        code,typ,wh,partner,source,status,day,items=row
        if status=='CONFIRMED':
            for p,qty in items: move(wh,p,'IMPORT','IMPORT_RECEIPT',i,qty,day,code)
    elif kind=='export':
        code,typ,wh,partner,source,status,day,items=row
        if status=='CONFIRMED':
            for p,qty in items: move(wh,p,'EXPORT','EXPORT_RECEIPT',i,-qty,day,code)
    elif kind=='transfer':
        code,a,b,status,day,items=row
        if status=='CONFIRMED':
            for p,qty in items:
                move(a,p,'TRANSFER_OUT','TRANSFER',i,-qty,day,code)
                move(b,p,'TRANSFER_IN','TRANSFER',i,qty,day,code)
    elif kind=='check':
        code,wh,status,day,items=row
        check_rows.append((code,wh,2,1 if status=='CONFIRMED' else None,Raw(date(day)),f'Kiểm kê {code}',status))
        for p,total_change,damage_change,obsolete_change in items:
            before=stock[wh,p]; db=damaged[wh,p]; ob=obsolete[wh,p]
            actual=before+total_change; da=db+damage_change; oa=ob+obsolete_change
            assert actual>=da+oa
            check_detail.append((i,p,before,actual,db,ob,actual-da-oa,da,oa,'Phân loại hàng theo tình trạng'))
            if status=='CONFIRMED':
                if total_change: move(wh,p,'ADJUSTMENT','STOCK_CHECK',i,total_change,day,code)
                if damage_change: move(wh,p,'DAMAGE_ADJUSTMENT','STOCK_CHECK',i,damage_change,day,code)
                if obsolete_change: move(wh,p,'OBSOLETE_ADJUSTMENT','STOCK_CHECK',i,obsolete_change,day,code)
    else:
        code,wh,check,status,day,items=row
        adjust_rows.append((code,wh,check,2,1 if status=='CONFIRMED' else None,Raw(date(day)),f'Điều chỉnh {code}',status))
        for p,total_change,damage_change,obsolete_change in items:
            before=stock[wh,p]; actual=before+total_change
            adjust_detail.append((i,p,before,actual,total_change,products[p-1][4],damage_change,obsolete_change,'Điều chỉnh hàng mẫu'))
            if status=='CONFIRMED':
                if total_change: move(wh,p,'ADJUSTMENT','ADJUSTMENT',i,total_change,day,code)
                if damage_change: move(wh,p,'DAMAGE_ADJUSTMENT','ADJUSTMENT',i,damage_change,day,code)
                if obsolete_change: move(wh,p,'OBSOLETE_ADJUSTMENT','ADJUSTMENT',i,obsolete_change,day,code)
check_rows.sort(key=lambda row: row[0])
check_detail.sort(key=lambda row: (row[0],row[1]))
adjust_rows.sort(key=lambda row: row[0])
adjust_detail.sort(key=lambda row: (row[0],row[1]))
insert('stock_checks',['check_code','warehouse_id','created_by','approved_by','check_date','note','status'],check_rows)
insert('stock_check_details',['check_id','product_id','system_quantity','actual_quantity','system_damaged_quantity','system_obsolete_quantity','good_quantity','damaged_quantity','obsolete_quantity','note'],check_detail)
insert('adjustment_receipts',['adjustment_code','warehouse_id','check_id','created_by','approved_by','adjustment_date','reason','status'],adjust_rows)
insert('adjustment_details',['adjustment_id','product_id','system_quantity','actual_quantity','quantity_change','unit_price','damaged_change','obsolete_change','note'],adjust_detail)
keys=sorted(set(stock)|set(damaged)|set(obsolete))
insert('inventory',['warehouse_id','product_id','quantity','damaged_quantity','obsolete_quantity'],[(wh,p,stock[wh,p],damaged[wh,p],obsolete[wh,p]) for wh,p in keys])
insert('inventory_transactions',['warehouse_id','product_id','created_by','transaction_type','reference_type','reference_id','quantity_before','quantity_change','quantity_after','transaction_date','note'],movements)

# Invoice totals are calculated from the exact source receipt lines.
invoice_specs=[
('INPUT',1,'ISSUED',-53,-7),('INPUT',2,'ISSUED',-49,21),('INPUT',3,'ISSUED',-45,-5),('INPUT',4,'ISSUED',-41,14),('INPUT',5,'ISSUED',-37,24),('INPUT',6,'ISSUED',-32,18),('INPUT',7,'DRAFT',-27,25),('INPUT',8,'CANCELLED',-23,20),
('OUTPUT',1,'ISSUED',-19,-2),('OUTPUT',2,'ISSUED',-17,18),('OUTPUT',3,'ISSUED',-15,15),('OUTPUT',4,'ISSUED',-13,20),('OUTPUT',5,'ISSUED',-10,24),('OUTPUT',6,'ISSUED',-8,25),('OUTPUT',7,'ISSUED',-6,17),('OUTPUT',8,'ISSUED',-4,30),('OUTPUT',13,'DRAFT',-1,30),('OUTPUT',14,'CANCELLED',-12,18)]
invoice_rows=[];invoice_details=[];invoice_amounts={}
for i,(typ,source_id,status,day,due) in enumerate(invoice_specs,1):
    source=(imports if typ=='INPUT' else exports)[source_id-1]
    partner=source[3]
    ptable=suppliers if typ=='INPUT' else customers
    party=ptable[partner-1]
    items=source[-1]
    subtotal=0
    for p,qty in items:
        unit=products[p-1][4 if typ=='INPUT' else 5]
        sub=qty*unit;vat=sub//10;subtotal+=sub
        invoice_details.append((i,p,qty,unit,sub,10,vat,sub+vat))
    number=f"{'HDV' if typ=='INPUT' else 'HDRA'}{i:06}"
    invoice_amounts[i]=subtotal*11//10
    invoice_rows.append((number,typ,Raw(date(day)),Raw(date(due)),partner if typ=='INPUT' else None,partner if typ=='OUTPUT' else None,source_id if typ=='INPUT' else None,source_id if typ=='OUTPUT' else None,party[1] if typ=='INPUT' else 'StockFlow',party[2] if typ=='INPUT' else '0309000001','StockFlow' if typ=='INPUT' else party[1],'0309000001' if typ=='INPUT' else party[2],subtotal,10,subtotal//10,invoice_amounts[i],status,1))
insert('vat_invoices',['invoice_number','invoice_type','invoice_date','due_date','supplier_id','customer_id','import_id','export_id','seller_name','seller_tax_code','buyer_name','buyer_tax_code','subtotal','vat_rate','vat_amount','total_amount','status','created_by'],invoice_rows)
insert('vat_invoice_details',['invoice_id','product_id','quantity','unit_price','subtotal','vat_rate','vat_amount','total_amount'],invoice_details)

# Opening balances are independent brought-forward positions, with status audit fields.
openings=[('CUSTOMER',1,'TRADE_RECEIVABLE',18000000,0,'CONFIRMED'),('CUSTOMER',2,'OTHER_RECEIVABLE',4500000,0,'CONFIRMED'),('CUSTOMER',3,'RESERVE_CAPITAL',2600000,0,'DRAFT'),('CUSTOMER',4,'SHORTFALL',850000,0,'CANCELLED'),('SUPPLIER',1,'TRADE_PAYABLE',0,12500000,'CONFIRMED'),('SUPPLIER',2,'EXTERNAL',0,7400000,'CONFIRMED'),('SUPPLIER',3,'INTERNAL',0,2800000,'DRAFT'),('SUPPLIER',4,'OTHER_PAYABLE',0,1600000,'CANCELLED')]
insert('debt_opening_balances',['fiscal_year','effective_date','partner_type','customer_id','supplier_id','category','budget_chapter','budget_type','budget_section','budget_item','debit_amount','credit_amount','reference_code','note','status','created_by','confirmed_by','confirmed_at','cancelled_by','cancelled_at'],[(Raw('YEAR(@seed_date)'),Raw('MAKEDATE(YEAR(@seed_date), 1)'),typ,party if typ=='CUSTOMER' else None,party if typ=='SUPPLIER' else None,category,'001','01','001','001',debit,credit,f'SD-{i:03}','Số dư đầu kỳ mẫu',status,1,1 if status=='CONFIRMED' else None,Raw(dt(-69)) if status=='CONFIRMED' else None,1 if status=='CANCELLED' else None,Raw(dt(-68)) if status=='CANCELLED' else None) for i,(typ,party,category,debit,credit,status) in enumerate(openings,1)])

ledger=[]
def entry(day,typ,partner,category,debit,credit,source,ref_type,ref_id,ref_code,status,description):
    ledger.append((Raw(date(day)),typ,partner if typ=='CUSTOMER' else None,partner if typ=='SUPPLIER' else None,category,debit,credit,source,ref_type,ref_id,ref_code,description,status,1,1 if status=='CONFIRMED' else None,Raw(dt(day)) if status=='CONFIRMED' else None,1 if status=='CANCELLED' else None,Raw(dt(day)) if status=='CANCELLED' else None))
# Invoice postings are illustrative debt ledger records; invoice reports read the
# invoice itself and allocate only confirmed PAYMENT entries.
for inv_id in [1,2,3,4,5,6,9,10,11,12,13,14,15,16]:
    typ,source_id,status,day,due=invoice_specs[inv_id-1]
    party=(imports if typ=='INPUT' else exports)[source_id-1][3]
    num=invoice_rows[inv_id-1][0]
    entry(day,'SUPPLIER' if typ=='INPUT' else 'CUSTOMER',party,'TRADE_PAYABLE' if typ=='INPUT' else 'TRADE_RECEIVABLE',0 if typ=='INPUT' else invoice_amounts[inv_id],invoice_amounts[inv_id] if typ=='INPUT' else 0,'INVOICE','VAT_INVOICE',inv_id,num,'CONFIRMED',f'Ghi nhận hóa đơn {num}')
# Fully paid, partly paid, unpaid and overdue states across both invoice views.
for inv_id,amount,day in [(2,invoice_amounts[2]//2,-20),(3,invoice_amounts[3],-12),(4,invoice_amounts[4]//3,-7),(9,invoice_amounts[9]//2,-10),(10,invoice_amounts[10],-5),(12,invoice_amounts[12]//4,-2),(14,invoice_amounts[14],-1)]:
    typ,source_id,*_=invoice_specs[inv_id-1]
    party=(imports if typ=='INPUT' else exports)[source_id-1][3]
    num=invoice_rows[inv_id-1][0]
    entry(day,'SUPPLIER' if typ=='INPUT' else 'CUSTOMER',party,'TRADE_PAYABLE' if typ=='INPUT' else 'TRADE_RECEIVABLE',amount if typ=='INPUT' else 0,0 if typ=='INPUT' else amount,'PAYMENT','VAT_INVOICE',inv_id,num,'CONFIRMED',f'Thanh toán một phần/toàn bộ {num}')
entry(-3,'CUSTOMER',3,'OTHER_RECEIVABLE',750000,0,'MANUAL',None,None,'PT-001','CONFIRMED','Phí vận chuyển phát sinh')
entry(-2,'SUPPLIER',5,'OTHER_PAYABLE',0,380000,'ADJUSTMENT',None,None,'DC-CN-001','CONFIRMED','Điều chỉnh công nợ')
entry(-1,'CUSTOMER',4,'TRADE_RECEIVABLE',900000,0,'MANUAL',None,None,'PT-002','DRAFT','Đề nghị ghi nhận công nợ')
entry(-9,'SUPPLIER',3,'TRADE_PAYABLE',0,210000,'MANUAL',None,None,'PT-003','CANCELLED','Bút toán đã hủy')
insert('debt_ledger_entries',['entry_date','partner_type','customer_id','supplier_id','category','debit_amount','credit_amount','entry_source','reference_type','reference_id','reference_code','description','status','created_by','confirmed_by','confirmed_at','cancelled_by','cancelled_at'],ledger)

audit=[]
for user,resource,resource_id,action,method,path,day in [
    (1,'auth',None,'LOGIN','POST','/api/auth/login',-56),
    (1,'products',1,'CREATE','POST','/api/products',-56),
    (1,'import_receipts',4,'CONFIRM','POST','/api/imports/4/confirm',-43),
    (1,'export_receipts',1,'CONFIRM','POST','/api/exports/1/confirm',-20),
    (1,'transfer_receipts',1,'CONFIRM','POST','/api/transfers/1/confirm',-13),
    (1,'stock_checks',1,'CONFIRM','POST','/api/stock-checks/1/confirm',-4),
    (1,'adjustment_receipts',2,'CONFIRM','POST','/api/adjustments/2/confirm',-1),
    (2,'vat_invoices',18,'CREATE','POST','/api/vat-invoices',-4),
    (1,'debt_ledger_entries',1,'CONFIRM','POST','/api/debt/ledger/1/confirm',-3),
    (2,'inventory',None,'READ','GET','/api/inventory',0),
]:
    audit.append((user,action,resource,str(resource_id) if resource_id else None,method,path,200,'127.0.0.1',Raw("JSON_OBJECT('demo', TRUE, 'source', 'seed')"),Raw(dt(day,10))))
insert('audit_logs',['user_id','action','resource','resource_id','method','path','status_code','ip_address','metadata','created_at'],audit)
lines.append('COMMIT;')
OUT.write_text('\n'.join(lines)+'\n',encoding='utf-8')
print(f'Wrote {OUT}: {len(products)} products, {len(movements)} stock transactions, {len(invoice_rows)} VAT invoices, {len(ledger)} debt entries')
