BEGIN;

ALTER TABLE IF EXISTS inventory.inventory_variance_cases
    ADD COLUMN IF NOT EXISTS accounting_journal_id bigint,
    ADD COLUMN IF NOT EXISTS accounting_journal_no varchar(100),
    ADD COLUMN IF NOT EXISTS inventory_account_code varchar(40),
    ADD COLUMN IF NOT EXISTS variance_gain_account_code varchar(40),
    ADD COLUMN IF NOT EXISTS variance_loss_account_code varchar(40);

CREATE INDEX IF NOT EXISTS idx_inventory_variance_cases_accounting
    ON inventory.inventory_variance_cases (tenant_id, accounting_journal_id);

INSERT INTO accounting.account_types (
    tenant_id,
    type_code,
    type_name,
    category,
    account_type_code,
    account_type_name,
    normal_balance,
    statement_type,
    display_order,
    is_active,
    is_deleted,
    metadata,
    created_at,
    updated_at
)
SELECT
    t.id,
    seed.type_code,
    seed.type_name,
    seed.category,
    seed.type_code,
    seed.type_name,
    seed.normal_balance,
    seed.statement_type,
    seed.display_order,
    TRUE,
    FALSE,
    '{"source":"inventory_variance_accounting_alignment"}'::jsonb,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM platform.tenants t
CROSS JOIN (
    VALUES
        ('REVENUE', 'Revenue', 'income_statement', 'credit', 'income_statement', 40),
        ('EXPENSE', 'Expense', 'income_statement', 'debit', 'income_statement', 50)
) AS seed(type_code, type_name, category, normal_balance, statement_type, display_order)
WHERE t.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM accounting.account_types at
      WHERE at.tenant_id = t.id
        AND COALESCE(at.is_deleted, FALSE) = FALSE
        AND (
             LOWER(COALESCE(at.account_type_code, '')) = LOWER(seed.type_code)
          OR LOWER(COALESCE(at.type_code, '')) = LOWER(seed.type_code)
          OR LOWER(COALESCE(at.account_type_name, '')) = LOWER(seed.type_name)
          OR LOWER(COALESCE(at.type_name, '')) = LOWER(seed.type_name)
        )
  );

INSERT INTO accounting.chart_of_accounts (
    tenant_id,
    account_type_id,
    account_code,
    account_name,
    account_type,
    normal_balance,
    currency_code,
    description,
    level,
    is_postable,
    status,
    is_active,
    is_deleted,
    metadata,
    created_at,
    updated_at
)
SELECT
    t.id,
    at.id,
    seed.account_code,
    seed.account_name,
    seed.account_type,
    seed.normal_balance,
    COALESCE(tls.currency_code, t.currency, 'GHS'),
    seed.description,
    1,
    TRUE,
    'active',
    TRUE,
    FALSE,
    '{"source":"inventory_variance_accounting_alignment"}'::jsonb,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM platform.tenants t
LEFT JOIN platform.tenant_localization_settings tls
    ON tls.tenant_id = t.id
   AND tls.deleted_at IS NULL
CROSS JOIN (
    VALUES
        ('4210', 'Inventory Adjustment Gain', 'Revenue', 'credit', 'Gains from approved positive inventory count variances.'),
        ('8320', 'Inventory Adjustment Loss', 'Expense', 'debit', 'Losses from approved negative inventory count variances.')
) AS seed(account_code, account_name, account_type, normal_balance, description)
JOIN LATERAL (
    SELECT id
    FROM accounting.account_types at
    WHERE at.tenant_id = t.id
      AND COALESCE(at.is_deleted, FALSE) = FALSE
      AND (
           LOWER(COALESCE(at.account_type_code, '')) = LOWER(seed.account_type)
        OR LOWER(COALESCE(at.type_code, '')) = LOWER(seed.account_type)
        OR LOWER(COALESCE(at.account_type_name, '')) = LOWER(seed.account_type)
        OR LOWER(COALESCE(at.type_name, '')) = LOWER(seed.account_type)
      )
    ORDER BY id
    LIMIT 1
) at ON TRUE
WHERE t.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM accounting.chart_of_accounts coa
      WHERE coa.tenant_id = t.id
        AND coa.account_code = seed.account_code
        AND COALESCE(coa.is_deleted, FALSE) = FALSE
  );

COMMIT;