"""Add cloud sync outbox, ledger, and deployment node sync columns."""
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))

from sqlalchemy import text
from app.core.database import engine


def run():
    stmts = [
        """
        CREATE TABLE IF NOT EXISTS sync_outbox (
            id VARCHAR PRIMARY KEY,
            entity_type VARCHAR(64) NOT NULL,
            entity_id VARCHAR NOT NULL,
            operation VARCHAR(16) NOT NULL DEFAULT 'upsert',
            payload JSON NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            error_message TEXT,
            retry_count INTEGER DEFAULT 0,
            pushed_at TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """,
        "CREATE INDEX IF NOT EXISTS ix_sync_outbox_entity_type ON sync_outbox (entity_type)",
        "CREATE INDEX IF NOT EXISTS ix_sync_outbox_entity_id ON sync_outbox (entity_id)",
        "CREATE INDEX IF NOT EXISTS ix_sync_outbox_status ON sync_outbox (status)",
        """
        CREATE TABLE IF NOT EXISTS cloud_sync_ledger (
            id VARCHAR PRIMARY KEY,
            node_id VARCHAR NOT NULL REFERENCES deployment_nodes(id),
            company_id VARCHAR REFERENCES companies(id),
            outbox_id VARCHAR,
            entity_type VARCHAR(64) NOT NULL,
            entity_id VARCHAR NOT NULL,
            operation VARCHAR(16) DEFAULT 'upsert',
            payload JSON,
            apply_status VARCHAR(20) DEFAULT 'applied',
            error_message TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
        """,
        "CREATE INDEX IF NOT EXISTS ix_cloud_sync_ledger_node_id ON cloud_sync_ledger (node_id)",
        "CREATE INDEX IF NOT EXISTS ix_cloud_sync_ledger_company_id ON cloud_sync_ledger (company_id)",
    ]
    alter_nodes = [
        "ALTER TABLE deployment_nodes ADD COLUMN IF NOT EXISTS sync_requested_at TIMESTAMP",
        "ALTER TABLE deployment_nodes ADD COLUMN IF NOT EXISTS last_sync_at TIMESTAMP",
        "ALTER TABLE deployment_nodes ADD COLUMN IF NOT EXISTS last_sync_status VARCHAR(32)",
        "ALTER TABLE deployment_nodes ADD COLUMN IF NOT EXISTS last_sync_error TEXT",
        "ALTER TABLE deployment_nodes ADD COLUMN IF NOT EXISTS last_sync_events_count INTEGER DEFAULT 0",
        "ALTER TABLE deployment_nodes ADD COLUMN IF NOT EXISTS realtime_sync_enabled BOOLEAN DEFAULT TRUE",
    ]
    with engine.begin() as conn:
        for sql in stmts:
            try:
                conn.execute(text(sql))
            except Exception as e:
                print(f"Note: {e}")
        for sql in alter_nodes:
            try:
                conn.execute(text(sql))
            except Exception:
                pass
    print("Cloud sync migration complete.")


if __name__ == "__main__":
    run()
