"""sync production schema to current state"""

from alembic import op
import sqlalchemy as sa


# revision = "db4d52ed2310"
# down_revision = "e62820b19c5e"
# branch_labels = None
# depends_on = None

# # revision identifiers, used by Alembic.
revision: str = 'e62820b19c5e'
# down_revision: Union[str, None] = None
# branch_labels: Union[str, Sequence[str], None] = None
# depends_on: Union[str, Sequence[str], None] = None

down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    conn = op.get_bind()

    # ----------------------------------------------------
    # ORGANIZATIONS - SAFE CREATE
    # ----------------------------------------------------
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS organizations (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            name VARCHAR(120) NOT NULL,
            slug VARCHAR(60) UNIQUE NOT NULL,
            plan VARCHAR(20),
            max_users INTEGER,
            max_products INTEGER,
            allow_public_signup BOOLEAN DEFAULT false,
            is_active BOOLEAN DEFAULT true,
            is_paused BOOLEAN DEFAULT false,
            subscription_status VARCHAR(20),
            subscription_ends_at TIMESTAMP,
            owner_id UUID,
            created_at TIMESTAMP DEFAULT now(),
            updated_at TIMESTAMP DEFAULT now(),
            deleted BOOLEAN DEFAULT false
        )
    """))

    # ----------------------------------------------------
    # USERS ORGANIZATION RELATION (SAFE)
    # ----------------------------------------------------
    conn.execute(sa.text("""
        ALTER TABLE users
        ADD COLUMN IF NOT EXISTS organization_id UUID
    """))

    conn.execute(sa.text("""
        UPDATE users
        SET organization_id = (
            SELECT id FROM organizations ORDER BY created_at LIMIT 1
        )
        WHERE organization_id IS NULL
    """))

    conn.execute(sa.text("""
        ALTER TABLE users
        ALTER COLUMN organization_id SET NOT NULL
    """))

    conn.execute(sa.text("""
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint WHERE conname = 'fk_users_org'
            ) THEN
                ALTER TABLE users
                ADD CONSTRAINT fk_users_org
                FOREIGN KEY (organization_id)
                REFERENCES organizations(id)
                ON DELETE CASCADE;
            END IF;
        END $$;
    """))

    # ----------------------------------------------------
    # SAFE INDEX CREATION
    # ----------------------------------------------------
    conn.execute(sa.text("""
        CREATE INDEX IF NOT EXISTS ix_users_organization_id
        ON users(organization_id)
    """))


def downgrade():
    conn = op.get_bind()

    conn.execute(sa.text("""
        ALTER TABLE users DROP CONSTRAINT IF EXISTS fk_users_org
    """))

    conn.execute(sa.text("""
        ALTER TABLE users DROP COLUMN IF EXISTS organization_id
    """))

    conn.execute(sa.text("""
        DROP TABLE IF EXISTS organizations
    """))