from __future__ import annotations

from sqlite3 import Connection


def create_postfinance_ledger_import_v1(conn: Connection) -> None:
    """Create immutable PostFinance source, ledger, snapshot and cost-basis tables."""
    position_columns = {row[1] for row in conn.execute("PRAGMA table_info(positions_snapshot)")}
    if "source_type" not in position_columns:
        conn.execute("ALTER TABLE positions_snapshot ADD COLUMN source_type TEXT")
    if "source_reference" not in position_columns:
        conn.execute("ALTER TABLE positions_snapshot ADD COLUMN source_reference TEXT")
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS postfinance_account_roles (
            role TEXT PRIMARY KEY CHECK(role IN ('efinance','etrading_depot','etrading_cash')),
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            source_reference_hash TEXT,
            created_at TEXT NOT NULL,
            UNIQUE(account_id, role)
        );
        CREATE TABLE IF NOT EXISTS postfinance_import_batches (
            batch_id TEXT PRIMARY KEY,
            bundle_sha256 TEXT NOT NULL UNIQUE CHECK(length(bundle_sha256)=64),
            zip_sha256 TEXT NOT NULL CHECK(length(zip_sha256)=64),
            overview_sha256 TEXT NOT NULL CHECK(length(overview_sha256)=64),
            parser_id TEXT NOT NULL,
            parser_version TEXT NOT NULL,
            db_revision TEXT NOT NULL,
            archive_reference TEXT NOT NULL,
            snapshot_at TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status='confirmed'),
            audit_id TEXT NOT NULL,
            confirmed_at TEXT NOT NULL,
            confirmed_by TEXT NOT NULL DEFAULT 'user'
        );
        CREATE TABLE IF NOT EXISTS postfinance_documents (
            document_hash TEXT PRIMARY KEY CHECK(length(document_hash)=64),
            filename_hash TEXT NOT NULL CHECK(length(filename_hash)=64),
            semantic_identity TEXT NOT NULL UNIQUE CHECK(length(semantic_identity)=64),
            semantic_reference_hash TEXT NOT NULL CHECK(length(semantic_reference_hash)=64),
            document_type TEXT NOT NULL,
            document_date TEXT,
            account_reference_hash TEXT,
            account_role TEXT NOT NULL CHECK(account_role IN ('efinance','etrading_depot','etrading_cash')),
            page_count INTEGER NOT NULL CHECK(page_count > 0),
            archive_reference TEXT NOT NULL,
            parser_version TEXT NOT NULL,
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS postfinance_batch_documents (
            batch_id TEXT NOT NULL REFERENCES postfinance_import_batches(batch_id),
            document_hash TEXT NOT NULL REFERENCES postfinance_documents(document_hash),
            PRIMARY KEY(batch_id, document_hash)
        );
        CREATE TABLE IF NOT EXISTS postfinance_snapshots (
            snapshot_id TEXT PRIMARY KEY,
            batch_id TEXT NOT NULL UNIQUE REFERENCES postfinance_import_batches(batch_id),
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            snapshot_at TEXT NOT NULL,
            total_chf TEXT NOT NULL,
            securities_chf TEXT NOT NULL,
            cash_chf TEXT NOT NULL,
            stocks_chf TEXT NOT NULL,
            etfs_chf TEXT NOT NULL,
            component_total_chf TEXT NOT NULL,
            difference_chf TEXT NOT NULL,
            tolerance_chf TEXT NOT NULL,
            position_count INTEGER NOT NULL,
            cash_count INTEGER NOT NULL,
            open_orders INTEGER NOT NULL,
            reconciliation_status TEXT NOT NULL CHECK(reconciliation_status IN ('matched','within_tolerance')),
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS postfinance_snapshot_positions (
            snapshot_position_id TEXT PRIMARY KEY,
            snapshot_id TEXT NOT NULL REFERENCES postfinance_snapshots(snapshot_id),
            source_row_reference TEXT NOT NULL,
            instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            asset_class TEXT NOT NULL CHECK(asset_class IN ('stock','etf')),
            quantity TEXT NOT NULL,
            provider_average_cost_original TEXT,
            provider_cost_total_original TEXT,
            price_original TEXT NOT NULL,
            price_currency TEXT NOT NULL,
            value_chf TEXT NOT NULL,
            weight_pct TEXT NOT NULL,
            computed_cost_basis_original TEXT,
            computed_cost_basis_status TEXT NOT NULL CHECK(computed_cost_basis_status IN ('complete','partial','unavailable')),
            provenance_json TEXT NOT NULL CHECK(json_valid(provenance_json)),
            created_at TEXT NOT NULL,
            UNIQUE(snapshot_id, source_row_reference),
            UNIQUE(snapshot_id, instrument_id)
        );
        CREATE TABLE IF NOT EXISTS postfinance_snapshot_cash (
            snapshot_cash_id TEXT PRIMARY KEY,
            snapshot_id TEXT NOT NULL REFERENCES postfinance_snapshots(snapshot_id),
            currency TEXT NOT NULL,
            amount_original TEXT NOT NULL,
            fx_rate_to_chf TEXT NOT NULL,
            value_chf TEXT NOT NULL,
            created_at TEXT NOT NULL,
            UNIQUE(snapshot_id, currency)
        );
        CREATE TABLE IF NOT EXISTS postfinance_ledger_events (
            event_id TEXT PRIMARY KEY,
            event_fingerprint TEXT NOT NULL UNIQUE CHECK(length(event_fingerprint)=64),
            document_hash TEXT NOT NULL REFERENCES postfinance_documents(document_hash),
            semantic_reference_hash TEXT NOT NULL CHECK(length(semantic_reference_hash)=64),
            event_type TEXT NOT NULL CHECK(event_type IN ('buy','sell','dividend','interest','fee','internal_transfer','fx','split','corporate_action')),
            account_role TEXT NOT NULL CHECK(account_role IN ('efinance','etrading_depot','etrading_cash')),
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            occurred_on TEXT NOT NULL,
            settlement_on TEXT,
            direction TEXT NOT NULL CHECK(direction IN ('in','out','neutral')),
            instrument_id TEXT REFERENCES instruments(instrument_id),
            quantity TEXT,
            price_original TEXT,
            gross_original TEXT,
            fee_original TEXT NOT NULL DEFAULT '0',
            tax_original TEXT NOT NULL DEFAULT '0',
            net_original TEXT,
            currency TEXT,
            fx_rate_to_chf TEXT,
            internal_transfer_group TEXT,
            quality_status TEXT NOT NULL CHECK(quality_status IN ('complete','partial')),
            reason_codes_json TEXT NOT NULL CHECK(json_valid(reason_codes_json)),
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_postfinance_events_time ON postfinance_ledger_events(occurred_on, event_type);
        CREATE INDEX IF NOT EXISTS idx_postfinance_events_transfer ON postfinance_ledger_events(internal_transfer_group) WHERE internal_transfer_group IS NOT NULL;
        CREATE TABLE IF NOT EXISTS postfinance_event_components (
            component_id TEXT PRIMARY KEY,
            event_id TEXT NOT NULL REFERENCES postfinance_ledger_events(event_id),
            component_type TEXT NOT NULL CHECK(component_type IN ('gross','fee','tax','net','fx_amount')),
            amount_original TEXT NOT NULL,
            currency TEXT NOT NULL,
            provenance_document_hash TEXT NOT NULL REFERENCES postfinance_documents(document_hash),
            created_at TEXT NOT NULL,
            UNIQUE(event_id, component_type, currency)
        );
        CREATE TABLE IF NOT EXISTS postfinance_cost_basis_lots (
            lot_id TEXT PRIMARY KEY,
            event_id TEXT NOT NULL UNIQUE REFERENCES postfinance_ledger_events(event_id),
            instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            acquired_on TEXT NOT NULL,
            quantity_acquired TEXT NOT NULL,
            quantity_remaining TEXT NOT NULL,
            unit_cost_original TEXT NOT NULL,
            fees_original TEXT NOT NULL,
            currency TEXT NOT NULL,
            method TEXT NOT NULL CHECK(method='documented_trade_fifo'),
            provenance_document_hash TEXT NOT NULL REFERENCES postfinance_documents(document_hash),
            created_at TEXT NOT NULL
        );
        CREATE UNIQUE INDEX IF NOT EXISTS ux_account_value_postfinance_official_reference
            ON account_value_snapshots(source_reference)
            WHERE source_type='postfinance_official_import' AND source_reference IS NOT NULL;

        CREATE TRIGGER IF NOT EXISTS postfinance_batches_immutable BEFORE UPDATE ON postfinance_import_batches
        BEGIN SELECT RAISE(ABORT, 'postfinance import batches are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_batches_no_delete BEFORE DELETE ON postfinance_import_batches
        BEGIN SELECT RAISE(ABORT, 'postfinance import batches cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_documents_immutable BEFORE UPDATE ON postfinance_documents
        BEGIN SELECT RAISE(ABORT, 'postfinance documents are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_documents_no_delete BEFORE DELETE ON postfinance_documents
        BEGIN SELECT RAISE(ABORT, 'postfinance documents cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_batch_documents_immutable BEFORE UPDATE ON postfinance_batch_documents
        BEGIN SELECT RAISE(ABORT, 'postfinance batch document links are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_batch_documents_no_delete BEFORE DELETE ON postfinance_batch_documents
        BEGIN SELECT RAISE(ABORT, 'postfinance batch document links cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_snapshots_immutable BEFORE UPDATE ON postfinance_snapshots
        BEGIN SELECT RAISE(ABORT, 'postfinance snapshots are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_snapshots_no_delete BEFORE DELETE ON postfinance_snapshots
        BEGIN SELECT RAISE(ABORT, 'postfinance snapshots cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_snapshot_positions_immutable BEFORE UPDATE ON postfinance_snapshot_positions
        BEGIN SELECT RAISE(ABORT, 'postfinance snapshot positions are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_snapshot_positions_no_delete BEFORE DELETE ON postfinance_snapshot_positions
        BEGIN SELECT RAISE(ABORT, 'postfinance snapshot positions cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_snapshot_cash_immutable BEFORE UPDATE ON postfinance_snapshot_cash
        BEGIN SELECT RAISE(ABORT, 'postfinance snapshot cash is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_snapshot_cash_no_delete BEFORE DELETE ON postfinance_snapshot_cash
        BEGIN SELECT RAISE(ABORT, 'postfinance snapshot cash cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_events_immutable BEFORE UPDATE ON postfinance_ledger_events
        BEGIN SELECT RAISE(ABORT, 'postfinance ledger events are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_events_no_delete BEFORE DELETE ON postfinance_ledger_events
        BEGIN SELECT RAISE(ABORT, 'postfinance ledger events cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_event_components_immutable BEFORE UPDATE ON postfinance_event_components
        BEGIN SELECT RAISE(ABORT, 'postfinance event components are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_event_components_no_delete BEFORE DELETE ON postfinance_event_components
        BEGIN SELECT RAISE(ABORT, 'postfinance event components cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_cost_basis_immutable BEFORE UPDATE ON postfinance_cost_basis_lots
        BEGIN SELECT RAISE(ABORT, 'postfinance cost basis is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_cost_basis_no_delete BEFORE DELETE ON postfinance_cost_basis_lots
        BEGIN SELECT RAISE(ABORT, 'postfinance cost basis cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_account_values_immutable BEFORE UPDATE ON account_value_snapshots
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance account valuations are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_account_values_no_delete BEFORE DELETE ON account_value_snapshots
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance account valuations cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_positions_immutable BEFORE UPDATE ON positions_snapshot
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance position projections are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_positions_no_delete BEFORE DELETE ON positions_snapshot
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance position projections cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_cash_immutable BEFORE UPDATE ON cash_balances
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance cash projections are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_cash_no_delete BEFORE DELETE ON cash_balances
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance cash projections cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_transactions_immutable BEFORE UPDATE ON transactions
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance transactions are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_official_transactions_no_delete BEFORE DELETE ON transactions
        WHEN OLD.source_type='postfinance_official_import'
        BEGIN SELECT RAISE(ABORT, 'official postfinance transactions cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_audit_immutable_update
        BEFORE UPDATE ON audit_log WHEN OLD.entity_type='postfinance_import_batch'
        BEGIN SELECT RAISE(ABORT, 'postfinance import audit is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS postfinance_audit_no_delete
        BEFORE DELETE ON audit_log WHEN OLD.entity_type='postfinance_import_batch'
        BEGIN SELECT RAISE(ABORT, 'postfinance import audit cannot be deleted'); END;
        """
    )