"""rev_d - permissions, branches, chart of accounts seed Revision ID: 0003_rev_d Revises: 0002_rev_a Create Date: 2026-09-22 """ import hashlib from alembic import op, context import sqlalchemy as sa revision = "0003_rev_d" down_revision = "0002_rev_a" branch_labels = None depends_on = None BRANCHES = [ ("20000000-0000-4000-8000-000000000002", "BR-002", "Noymile Branch", "Noymile"), ("20000000-0000-4000-8000-000000000003", "BR-003", "Mohasthan Branch", "Mohasthan"), ("20000000-0000-4000-8000-000000000004", "BR-004", "Seed & Agro Input Shop", "Agro Input Shop"), ] COA = [ ("1010", "Cash on Hand", "asset", "D", "cash"), ("1020", "Bank Accounts", "asset", "D", "bank"), ("1030", "Accounts Receivable - Customers", "asset", "D", "receivable"), ("1040", "Inventory - Plants", "asset", "D", "inventory"), ("1045", "Inventory - Seedlings & Accessories", "asset", "D", "inventory"), ("1050", "Work In Progress - Production Batches", "asset", "D", "inventory"), ("1060", "Prepaid Expenses", "asset", "D", "prepaid"), ("1200", "Fixed Assets", "asset", "D", "fixed_asset"), ("1210", "Accumulated Depreciation", "asset", "C", "contra_asset"), ("2010", "Accounts Payable - Suppliers", "liability", "C", "payable"), ("2020", "VAT/Withholding Payable", "liability", "C", "payable"), ("2030", "Loans Payable", "liability", "C", "loan"), ("2040", "Accrued Expenses", "liability", "C", "accrual"), ("2050", "Customer Advances", "liability", "C", "advance"), ("3010", "Opening Balance Equity", "equity", "C", "equity"), ("3020", "Owner's Capital", "equity", "C", "equity"), ("3030", "Owner's Drawings", "equity", "D", "equity"), ("3090", "Retained Earnings", "equity", "C", "equity"), ("4010", "Sales - Retail (plants)", "revenue", "C", "sales"), ("4020", "Sales - Wholesale", "revenue", "C", "sales"), ("4030", "Sales - Seed & Agro Inputs", "revenue", "C", "sales"), ("4040", "Sales Returns & Allowances", "revenue", "D", "sales_contra"), ("4050", "Discounts Given", "revenue", "D", "sales_contra"), ("4060", "Other Income / Services", "revenue", "C", "income"), ("5010", "COGS - Plants", "expense", "D", "cogs"), ("5020", "COGS - Accessories", "expense", "D", "cogs"), ("5030", "Purchase Discounts", "expense", "C", "cogs_contra"), ("6010", "Rent", "expense", "D", "expense"), ("6020", "Salary & Wages", "expense", "D", "expense"), ("6030", "Transport & Fuel", "expense", "D", "expense"), ("6040", "Electricity & Water", "expense", "D", "expense"), ("6050", "Internet & Communication", "expense", "D", "expense"), ("6060", "Marketing & Advertising", "expense", "D", "expense"), ("6070", "Packaging", "expense", "D", "expense"), ("6080", "Seeds & Propagation Material", "expense", "D", "expense"), ("6090", "Potting Media", "expense", "D", "expense"), ("6100", "Fertilizer & Nutrients", "expense", "D", "expense"), ("6110", "Plant Treatments & Agrochemicals", "expense", "D", "expense"), ("6120", "Nursery Overhead", "expense", "D", "expense"), ("6130", "Plant Loss / Mortality", "expense", "D", "expense"), ("6140", "Maintenance & Repairs", "expense", "D", "expense"), ("6150", "Bank Charges & Interest Expense", "expense", "D", "expense"), ("6160", "Depreciation", "expense", "D", "expense"), ("6990", "Other Expenses", "expense", "D", "expense"), ] ROLES = ["super_admin", "manager", "staff", "viewer"] ACTIONS = ["view", "create", "edit", "delete"] def _aid(code: str) -> str: return "a" + hashlib.md5(code.encode()).hexdigest()[:33] def upgrade() -> None: if context.is_offline_mode(): return bind = op.get_bind() for branch_id, code, name, address in BRANCHES: existing = bind.execute( sa.text("SELECT id FROM branches WHERE code = :code"), {"code": code}, ).fetchone() if not existing: bind.execute( sa.text( "INSERT INTO branches (id, code, name, address, is_active, created_at, updated_at) " "VALUES (:id, :code, :name, :address, 1, now(), now())" ), {"id": branch_id, "code": code, "name": name, "address": address}, ) bind.execute(sa.text("DELETE FROM permissions WHERE module = 'accounting'")) perm_rows = [] for action in ACTIONS: perm_rows.append({"pid": _aid(f"accounting:{action}"), "module": "accounting", "action": action}) for row in perm_rows: bind.execute( sa.text("INSERT INTO permissions (id, module, action, created_at) VALUES (:id, :module, :action, now())"), {"id": row["pid"], "module": row["module"], "action": row["action"]}, ) perm_map = {row["action"]: row["pid"] for row in perm_rows} role_rows = [r for r in bind.execute(sa.text("SELECT id, name FROM roles")).fetchall() if r[1] in ROLES] for role_id, role_name in role_rows: if role_name == "viewer" or role_name == "staff": action_list = ["view"] else: action_list = ACTIONS for action in action_list: pid = perm_map[action] exists = bind.execute( sa.text( "SELECT 1 FROM role_permissions WHERE role_id = :role_id AND permission_id = :pid" ), {"role_id": role_id, "pid": pid}, ).fetchone() if not exists: bind.execute( sa.text( "INSERT INTO role_permissions (role_id, permission_id) VALUES (:role_id, :pid)" ), {"role_id": role_id, "pid": pid}, ) for code, name, account_type, normal_balance, category in COA: account_id = _aid(code) exists = bind.execute( sa.text("SELECT id FROM accounts WHERE code = :code"), {"code": code}, ).fetchone() if not exists: bind.execute( sa.text( "INSERT INTO accounts (id, name, type, code, account_type, normal_balance, " "category, is_system, balance, is_active, created_at) " "VALUES (:id, :name, :type, :code, :type, :balance, :category, 1, 0, 1, now())" ), {"id": account_id, "name": name, "code": code, "type": account_type, "balance": normal_balance, "category": category}, ) branch_rows = bind.execute(sa.text("SELECT id, code, name FROM branches WHERE is_active = 1")).fetchall() parent_map = {code: _aid(code) for code, _, _, _ in COA if code in ("1010", "1020")} for branch_id, branch_code, branch_name in branch_rows: for parent_code, parent_id in parent_map.items(): child_code = f"{parent_code}-{branch_code}" exists = bind.execute( sa.text("SELECT id FROM accounts WHERE code = :code"), {"code": child_code}, ).fetchone() if not exists: bind.execute( sa.text( "INSERT INTO accounts (id, name, type, code, account_type, normal_balance, " "category, parent_id, branch_id, is_system, balance, is_active, created_at) " "VALUES (:id, :name, :type, :code, :type, :balance, :category, :parent_id, " ":branch_id, 1, 0, 1, now())" ), {"id": _aid(child_code), "name": f"{parent_code} - {branch_name}", "type": "asset", "code": child_code, "balance": "D", "category": "cash" if parent_code == "1010" else "bank", "parent_id": parent_id, "branch_id": branch_id}, ) def downgrade() -> None: if context.is_offline_mode(): return bind = op.get_bind() bind.execute(sa.text("DELETE FROM accounts WHERE is_system = 1")) for _, code, _, _ in BRANCHES: bind.execute(sa.text("DELETE FROM branches WHERE code = :code"), {"code": code}) bind.execute(sa.text("DELETE FROM permissions WHERE module = 'accounting'"))