bmc_hub/migrations/228_migration_center.sql

-- Migration 228: Manual subscription and invoice migration centre.
-- e-conomic data is deliberately referenced from Invoice Error Finder and never copied back.

CREATE TABLE IF NOT EXISTS migration_center_sessions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('draft', 'active', 'completed', 'archived')),
    economic_import_run_id INTEGER REFERENCES invoice_error_finder_import_runs(id) ON DELETE RESTRICT,
    economic_snapshot_at TIMESTAMP,
    read_only BOOLEAN NOT NULL DEFAULT FALSE,
    created_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS migration_center_source_customers (
    id BIGSERIAL PRIMARY KEY,
    source_system VARCHAR(30) NOT NULL CHECK (source_system IN ('vtiger', 'simply', 'economic')),
    source_customer_id VARCHAR(120) NOT NULL,
    customer_no VARCHAR(80),
    customer_name VARCHAR(255) NOT NULL,
    cvr VARCHAR(32),
    email VARCHAR(255),
    raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    snapshot_hash CHAR(64) NOT NULL,
    hub_customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (source_system, source_customer_id)
);

CREATE TABLE IF NOT EXISTS migration_center_source_subscriptions (
    id BIGSERIAL PRIMARY KEY,
    source_system VARCHAR(30) NOT NULL CHECK (source_system IN ('vtiger', 'simply')),
    source_record_id VARCHAR(120) NOT NULL,
    source_customer_id VARCHAR(120),
    customer_no VARCHAR(80),
    customer_name VARCHAR(255),
    product_code VARCHAR(100),
    product_name VARCHAR(500) NOT NULL,
    amount NUMERIC(14,2) NOT NULL DEFAULT 0,
    quantity NUMERIC(14,4) NOT NULL DEFAULT 1,
    billing_frequency VARCHAR(40),
    start_date DATE,
    end_date DATE,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    snapshot_hash CHAR(64) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (source_system, source_record_id)
);

CREATE TABLE IF NOT EXISTS migration_center_session_items (
    id BIGSERIAL PRIMARY KEY,
    session_id INTEGER NOT NULL REFERENCES migration_center_sessions(id) ON DELETE CASCADE,
    entity_type VARCHAR(20) NOT NULL CHECK (entity_type IN ('subscription', 'invoice_line')),
    source_system VARCHAR(30) NOT NULL CHECK (source_system IN ('vtiger', 'simply', 'economic')),
    source_record_id VARCHAR(180) NOT NULL,
    source_customer_id VARCHAR(120),
    customer_no VARCHAR(80),
    customer_name VARCHAR(255),
    product_code VARCHAR(100),
    product_name VARCHAR(500) NOT NULL,
    amount NUMERIC(14,2) NOT NULL DEFAULT 0,
    quantity NUMERIC(14,4) NOT NULL DEFAULT 1,
    billing_frequency VARCHAR(40),
    period_from DATE,
    period_to DATE,
    invoice_no VARCHAR(80),
    invoice_date DATE,
    source_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    source_hash CHAR(64) NOT NULL,
    previous_source_payload JSONB,
    match_status VARCHAR(30) NOT NULL DEFAULT 'new'
        CHECK (match_status IN ('new', 'match_found', 'no_match', 'conflict', 'manual_review', 'source_changed')),
    approval_status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (approval_status IN ('pending', 'approved', 'rejected', 'verified', 'ignored')),
    hub_status VARCHAR(30) NOT NULL DEFAULT 'not_created'
        CHECK (hub_status IN ('not_created', 'ready_for_creation', 'created_in_hub', 'linked_to_existing', 'verified')),
    lock_status VARCHAR(25) NOT NULL DEFAULT 'unlocked'
        CHECK (lock_status IN ('unlocked', 'locking_pending', 'locked', 'lock_failed')),
    match_confidence NUMERIC(5,4),
    match_explanation JSONB NOT NULL DEFAULT '[]'::jsonb,
    hub_customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    hub_sag_id INTEGER REFERENCES sag_sager(id) ON DELETE SET NULL,
    hub_record_id INTEGER REFERENCES sag_subscriptions(id) ON DELETE SET NULL,
    suggested_hub_record_id INTEGER REFERENCES sag_subscriptions(id) ON DELETE SET NULL,
    creation_idempotency_key VARCHAR(120),
    ignore_reason TEXT,
    manual_note TEXT,
    verified_at TIMESTAMP,
    verified_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    locked_at TIMESTAMP,
    locked_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (session_id, entity_type, source_system, source_record_id)
);

ALTER TABLE migration_center_session_items
    ADD COLUMN IF NOT EXISTS creation_idempotency_key VARCHAR(120);

CREATE INDEX IF NOT EXISTS idx_mc_items_session_type ON migration_center_session_items(session_id, entity_type);
CREATE INDEX IF NOT EXISTS idx_mc_items_work_queue ON migration_center_session_items(session_id, lock_status, approval_status);
CREATE INDEX IF NOT EXISTS idx_mc_items_customer ON migration_center_session_items(session_id, hub_customer_id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_mc_items_creation_idempotency
    ON migration_center_session_items(session_id, creation_idempotency_key)
    WHERE creation_idempotency_key IS NOT NULL;

CREATE TABLE IF NOT EXISTS migration_center_matches (
    id BIGSERIAL PRIMARY KEY,
    session_item_id BIGINT NOT NULL REFERENCES migration_center_session_items(id) ON DELETE CASCADE,
    matched_entity_type VARCHAR(30) NOT NULL,
    matched_hub_id INTEGER NOT NULL,
    confidence NUMERIC(5,4) NOT NULL,
    rules JSONB NOT NULL DEFAULT '[]'::jsonb,
    approved BOOLEAN,
    approved_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    approved_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (session_item_id, matched_entity_type, matched_hub_id)
);

CREATE TABLE IF NOT EXISTS migration_center_lock_operations (
    id BIGSERIAL PRIMARY KEY,
    session_item_id BIGINT NOT NULL REFERENCES migration_center_session_items(id) ON DELETE CASCADE,
    status VARCHAR(25) NOT NULL CHECK (status IN ('pending', 'succeeded', 'failed')),
    attempt_no INTEGER NOT NULL DEFAULT 1,
    external_system VARCHAR(30),
    request_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    response_payload JSONB,
    error_message TEXT,
    requested_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS migration_center_audit_log (
    id BIGSERIAL PRIMARY KEY,
    session_id INTEGER REFERENCES migration_center_sessions(id) ON DELETE SET NULL,
    session_item_id BIGINT REFERENCES migration_center_session_items(id) ON DELETE SET NULL,
    entity_type VARCHAR(40) NOT NULL,
    entity_id VARCHAR(180),
    action VARCHAR(80) NOT NULL,
    old_value JSONB,
    new_value JSONB,
    source_hash CHAR(64),
    performed_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    ip_address VARCHAR(45),
    success BOOLEAN NOT NULL DEFAULT TRUE,
    error_message TEXT,
    performed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_mc_audit_session_time ON migration_center_audit_log(session_id, performed_at DESC);

ALTER TABLE sag_subscriptions
    ADD COLUMN IF NOT EXISTS migration_locked BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS migration_locked_at TIMESTAMP,
    ADD COLUMN IF NOT EXISTS migration_locked_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS migration_source_item_id BIGINT;

CREATE UNIQUE INDEX IF NOT EXISTS uq_sag_subscription_migration_source
    ON sag_subscriptions(migration_source_item_id)
    WHERE migration_source_item_id IS NOT NULL;

INSERT INTO permissions (code, description, category) VALUES
('migration_center.view', 'View migration centre', 'migration_center'),
('migration_center.sessions', 'Create and manage migration sessions', 'migration_center'),
('migration_center.import', 'Import CRM subscription snapshots', 'migration_center'),
('migration_center.create', 'Create customers, cases and subscriptions from migration centre', 'migration_center'),
('migration_center.review', 'Link, verify and ignore migration records', 'migration_center'),
('migration_center.lock', 'Lock migration records', 'migration_center'),
('migration_center.retry', 'Retry failed external locks', 'migration_center'),
('migration_center.export', 'Export migration control reports', 'migration_center')
ON CONFLICT (code) DO NOTHING;

INSERT INTO group_permissions (group_id, permission_id)
SELECT g.id, p.id
FROM groups g
JOIN permissions p ON p.category = 'migration_center'
WHERE g.name = 'Administrators'
ON CONFLICT DO NOTHING;