CREATE TABLE card_processor_configs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL UNIQUE,
    processor_name text NOT NULL,
    operating_model text NOT NULL CHECK (operating_model IN ('simulator', 'issuer_processor', 'processor_only', 'issuer_sponsor')),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'contracted', 'certified', 'disabled')),
    contract_reference text NOT NULL DEFAULT '',
    contract_signed_at timestamptz,
    api_base_url text NOT NULL DEFAULT '',
    webhook_signing_key_reference text NOT NULL DEFAULT '',
    certification_reference text NOT NULL DEFAULT '',
    real_traffic_enabled boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (real_traffic_enabled = false OR (status = 'certified' AND contract_reference <> '' AND certification_reference <> ''))
);

CREATE TRIGGER card_processor_configs_set_updated_at
BEFORE UPDATE ON card_processor_configs
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO card_processor_configs (
    provider, processor_name, operating_model, status, contract_reference,
    api_base_url, webhook_signing_key_reference, certification_reference, real_traffic_enabled
) VALUES (
    'local_card_issuer', 'Local development card issuer', 'simulator', 'disabled', '',
    'local://card-issuer', 'BANKING_CARD_WEBHOOK_SECRET', 'development-only', false
) ON CONFLICT (provider) DO NOTHING;

CREATE TABLE card_pci_scope_assessments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    assessment_name text NOT NULL,
    scope_status text NOT NULL CHECK (scope_status IN ('draft', 'in_review', 'approved', 'rejected')),
    assessor text NOT NULL DEFAULT '',
    qsa_reference text NOT NULL DEFAULT '',
    scope_model text NOT NULL CHECK (scope_model IN ('provider_hosted', 'token_only', 'cardholder_data_environment')),
    stores_pan boolean NOT NULL DEFAULT false,
    stores_cvv boolean NOT NULL DEFAULT false,
    pan_disclosure_allowed boolean NOT NULL DEFAULT false,
    evidence_reference text NOT NULL DEFAULT '',
    approved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    approved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (stores_cvv = false),
    CHECK (scope_status <> 'approved' OR (qsa_reference <> '' AND evidence_reference <> '' AND approved_at IS NOT NULL))
);

CREATE INDEX card_pci_scope_assessments_status_idx
    ON card_pci_scope_assessments (scope_status, created_at DESC);

CREATE TRIGGER card_pci_scope_assessments_set_updated_at
BEFORE UPDATE ON card_pci_scope_assessments
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE card_tokenization_policies (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    policy_name text NOT NULL UNIQUE,
    token_vault_provider text NOT NULL,
    token_type text NOT NULL CHECK (token_type IN ('issuer_token', 'network_token', 'processor_token')),
    key_reference text NOT NULL,
    rotation_interval_days integer NOT NULL CHECK (rotation_interval_days BETWEEN 1 AND 1095),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'retired')),
    pci_scope_assessment_id uuid REFERENCES card_pci_scope_assessments(id) ON DELETE SET NULL,
    notes text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX card_tokenization_policies_status_idx
    ON card_tokenization_policies (status, created_at DESC);

CREATE TRIGGER card_tokenization_policies_set_updated_at
BEFORE UPDATE ON card_tokenization_policies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO card_tokenization_policies (
    policy_name, token_vault_provider, token_type, key_reference, rotation_interval_days, status, notes
) VALUES (
    'local-issuer-token-policy', 'local_card_issuer', 'issuer_token', 'BANKING_CARD_SECRET', 180, 'active',
    'Development policy: store issuer token/external card id plus PAN fingerprint only; never persist CVV.'
) ON CONFLICT (policy_name) DO NOTHING;

ALTER TABLE virtual_cards
    ADD COLUMN provider_config_id uuid REFERENCES card_processor_configs(id) ON DELETE SET NULL,
    ADD COLUMN tokenization_policy_id uuid REFERENCES card_tokenization_policies(id) ON DELETE SET NULL,
    ADD COLUMN card_token text,
    ADD COLUMN tokenization_status text NOT NULL DEFAULT 'provider_tokenized'
        CHECK (tokenization_status IN ('provider_tokenized', 'token_pending', 'token_failed', 'retired')),
    ADD COLUMN token_key_reference text NOT NULL DEFAULT '',
    ADD COLUMN pci_scope_classification text NOT NULL DEFAULT 'metadata_only'
        CHECK (pci_scope_classification IN ('metadata_only', 'token_only', 'cardholder_data_environment'));

UPDATE virtual_cards
SET card_token = external_card_id,
    token_key_reference = 'BANKING_CARD_SECRET',
    provider_config_id = (SELECT id FROM card_processor_configs WHERE provider = 'local_card_issuer'),
    tokenization_policy_id = (SELECT id FROM card_tokenization_policies WHERE policy_name = 'local-issuer-token-policy')
WHERE card_token IS NULL;

ALTER TABLE virtual_cards
    ALTER COLUMN card_token SET NOT NULL;

CREATE UNIQUE INDEX virtual_cards_card_token_idx ON virtual_cards (card_token);

ALTER TABLE card_authorizations
    DROP CONSTRAINT IF EXISTS card_authorizations_status_check;

ALTER TABLE card_authorizations
    ADD COLUMN expires_at timestamptz,
    ADD COLUMN cleared_at timestamptz,
    ADD COLUMN reversed_at timestamptz,
    ADD COLUMN disputed_at timestamptz,
    ADD COLUMN ledger_status text NOT NULL DEFAULT 'pending'
        CHECK (ledger_status IN ('pending', 'posted', 'not_required', 'failed'));

UPDATE card_authorizations
SET expires_at = CASE WHEN status = 'approved' THEN created_at + interval '7 days' ELSE NULL END,
    ledger_status = CASE WHEN status = 'declined' THEN 'not_required' ELSE 'pending' END;

ALTER TABLE card_authorizations
    ADD CONSTRAINT card_authorizations_status_check CHECK (status IN (
        'approved', 'declined', 'cleared', 'reversed', 'expired', 'disputed'
    ));

CREATE INDEX card_authorizations_expires_at_idx
    ON card_authorizations (expires_at)
    WHERE status = 'approved';

CREATE TABLE card_authorization_ledger_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    card_authorization_id uuid NOT NULL REFERENCES card_authorizations(id) ON DELETE RESTRICT,
    event_type text NOT NULL CHECK (event_type IN ('hold', 'clearing', 'reversal', 'expiry', 'dispute_provisional_credit', 'dispute_reversal')),
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    ledger_journal_entry_id uuid REFERENCES ledger_journal_entries(id) ON DELETE SET NULL,
    status text NOT NULL DEFAULT 'posted' CHECK (status IN ('posted', 'skipped', 'failed')),
    reason text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX card_authorization_ledger_events_auth_type_idx
    ON card_authorization_ledger_events (card_authorization_id, event_type);

CREATE INDEX card_authorization_ledger_events_created_at_idx
    ON card_authorization_ledger_events (created_at DESC);

CREATE TABLE cardholder_notifications (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    virtual_card_id uuid REFERENCES virtual_cards(id) ON DELETE SET NULL,
    card_authorization_id uuid REFERENCES card_authorizations(id) ON DELETE SET NULL,
    card_dispute_id uuid,
    notification_type text NOT NULL CHECK (notification_type IN (
        'authorization_approved', 'authorization_declined', 'authorization_cleared',
        'authorization_reversed', 'authorization_expired', 'dispute_opened',
        'dispute_won', 'dispute_lost'
    )),
    channel text NOT NULL DEFAULT 'in_app' CHECK (channel IN ('in_app', 'email', 'push')),
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed', 'read')),
    title text NOT NULL,
    body text NOT NULL,
    sent_at timestamptz,
    read_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX cardholder_notifications_user_created_at_idx
    ON cardholder_notifications (user_id, created_at DESC);

CREATE TRIGGER cardholder_notifications_set_updated_at
BEFORE UPDATE ON cardholder_notifications
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE card_disputes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    virtual_card_id uuid NOT NULL REFERENCES virtual_cards(id) ON DELETE RESTRICT,
    card_authorization_id uuid NOT NULL REFERENCES card_authorizations(id) ON DELETE RESTRICT,
    reason_code text NOT NULL CHECK (reason_code IN ('fraud', 'duplicate', 'goods_not_received', 'service_not_provided', 'other')),
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'reviewing', 'submitted', 'won', 'lost', 'canceled')),
    description text NOT NULL DEFAULT '',
    provisional_credit boolean NOT NULL DEFAULT false,
    provisional_credit_ledger_event_id uuid REFERENCES card_authorization_ledger_events(id) ON DELETE SET NULL,
    decision_note text NOT NULL DEFAULT '',
    decided_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE cardholder_notifications
    ADD CONSTRAINT cardholder_notifications_dispute_fk
    FOREIGN KEY (card_dispute_id) REFERENCES card_disputes(id) ON DELETE SET NULL;

CREATE INDEX card_disputes_user_created_at_idx
    ON card_disputes (user_id, created_at DESC);

CREATE INDEX card_disputes_status_created_at_idx
    ON card_disputes (status, created_at DESC);

CREATE UNIQUE INDEX card_disputes_authorization_unique_idx
    ON card_disputes (card_authorization_id);

CREATE TRIGGER card_disputes_set_updated_at
BEFORE UPDATE ON card_disputes
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO privacy_data_inventory (
    table_name, field_name, data_category, classification, lawful_basis, retention_policy, residency_scope, encrypted_at_rest, owner, notes
) VALUES
    ('card_processor_configs', '*', 'audit', 'confidential', 'legal_obligation/security', 'audit_logs_default', 'default', false, 'cards', 'Card processor contract, certification and key-reference metadata; no secrets stored'),
    ('card_pci_scope_assessments', '*', 'audit', 'confidential', 'legal_obligation/security', 'audit_logs_default', 'default', false, 'security', 'PCI scope assessment and QSA evidence references'),
    ('card_tokenization_policies', '*', 'secret', 'restricted', 'security', 'secret_material_default', 'default', true, 'security', 'Token vault and key references only; no raw keys stored'),
    ('card_authorization_ledger_events', '*', 'financial', 'restricted', 'legal_obligation', 'financial_records_default', 'default', false, 'finance', 'Card hold, clearing, reversal, expiry and dispute ledger event references'),
    ('cardholder_notifications', '*', 'card', 'confidential', 'contract', 'card_metadata_default', 'customer_region', false, 'cards', 'Customer-visible card authorization and dispute notifications'),
    ('card_disputes', '*', 'financial', 'restricted', 'contract/legal_obligation', 'financial_records_default', 'customer_region', false, 'cards', 'Card dispute and provisional credit workflow records')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;