"""initial clean schema with organizations

Revision ID: 4fd0027e4625
Revises: 09265cf116c6
Create Date: 2026-06-12 01:40:00.000000
"""

from typing import Sequence, Union
from uuid import uuid4

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID

# revision: str = "4fd0027e4625"
# down_revision: Union[str, None] = "09265cf116c6"
# branch_labels: Union[str, Sequence[str], None] = None
# depends_on: Union[str, Sequence[str], None] = None

#""""
""""
Revises: e3f6cf7d42c5
Create Date: 2026-06-11 23:06:39.746638

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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

TABLES = [
"categories",
"items",
"sales",
"stock_history",
"expenses",
"roles",
"users",
]

def upgrade() -> None:
    conn = op.get_bind()

    # ------------------------------------------------------------------
    # 1. Create organizations table if it does not already exist
    # ------------------------------------------------------------------
    conn.execute(
        sa.text(
            """
            CREATE TABLE IF NOT EXISTS organizations (
                id UUID PRIMARY KEY,
                name VARCHAR NOT NULL,
                slug VARCHAR NOT NULL UNIQUE,
                plan VARCHAR,
                allow_public_signup BOOLEAN,
                is_active BOOLEAN,
                created_at TIMESTAMPTZ NOT NULL,
                updated_at TIMESTAMPTZ NOT NULL,
                deleted BOOLEAN
            )
            """
        )
    )

    conn.execute(
        sa.text(
            """
            CREATE INDEX IF NOT EXISTS ix_organizations_id
            ON organizations (id)
            """
        )
    )

    # ------------------------------------------------------------------
    # 2. Create default organization if missing
    # ------------------------------------------------------------------
    default_org_id = str(uuid4())

    conn.execute(
        sa.text(
            """
            INSERT INTO organizations (
                id,
                name,
                slug,
                plan,
                allow_public_signup,
                is_active,
                created_at,
                updated_at,
                deleted
            )
            SELECT
                :id,
                'Default Organization',
                'default-org',
                'free',
                FALSE,
                TRUE,
                NOW(),
                NOW(),
                FALSE
            WHERE NOT EXISTS (
                SELECT 1
                FROM organizations
                WHERE slug = 'default-org'
            )
            """
        ),
        {"id": default_org_id},
    )

    # ------------------------------------------------------------------
    # 3. Retrieve actual default organization id
    # ------------------------------------------------------------------
    org_id = conn.execute(
        sa.text(
            """
            SELECT id
            FROM organizations
            WHERE slug = 'default-org'
            LIMIT 1
            """
        )
    ).scalar()

    # ------------------------------------------------------------------
    # 4. Add organization_id column to all tables
    # ------------------------------------------------------------------
    for table in TABLES:
        conn.execute(
            sa.text(
                f"""
                ALTER TABLE {table}
                ADD COLUMN IF NOT EXISTS organization_id UUID
                """
            )
        )

        conn.execute(
            sa.text(
                f"""
                CREATE INDEX IF NOT EXISTS ix_{table}_organization_id
                ON {table} (organization_id)
                """
            )
        )

    # ------------------------------------------------------------------
    # 5. Backfill organization_id
    # ------------------------------------------------------------------
    for table in TABLES:
        conn.execute(
            sa.text(
                f"""
                UPDATE {table}
                SET organization_id = :org_id
                WHERE organization_id IS NULL
                """
            ),
            {"org_id": str(org_id)},
        )

    # ------------------------------------------------------------------
    # 6. Enforce NOT NULL
    # ------------------------------------------------------------------
    for table in TABLES:
        conn.execute(
            sa.text(
                f"""
                ALTER TABLE {table}
                ALTER COLUMN organization_id SET NOT NULL
                """
            )
        )

    # ------------------------------------------------------------------
    # 7. Create foreign keys safely
    # ------------------------------------------------------------------
    for table in TABLES:
        conn.execute(
            sa.text(
                f"""
                DO $$
                BEGIN
                    IF NOT EXISTS (
                        SELECT 1
                        FROM pg_constraint
                        WHERE conname = 'fk_{table}_org'
                    ) THEN
                        ALTER TABLE {table}
                        ADD CONSTRAINT fk_{table}_org
                        FOREIGN KEY (organization_id)
                        REFERENCES organizations(id)
                        ON DELETE CASCADE;
                    END IF;
                END
                $$;
                """
            )
        )

    # ------------------------------------------------------------------
    # 8. Add user columns safely
    # ------------------------------------------------------------------
    conn.execute(
        sa.text(
            """
            ALTER TABLE users
            ADD COLUMN IF NOT EXISTS email_verified BOOLEAN
            """
        )
    )

    conn.execute(
        sa.text(
            """
            ALTER TABLE users
            ADD COLUMN IF NOT EXISTS last_seen TIMESTAMPTZ
            """
        )
    )

    conn.execute(
        sa.text(
            """
            ALTER TABLE users
            ADD COLUMN IF NOT EXISTS is_online BOOLEAN
            """
        )
    )

    conn.execute(
        sa.text(
            """
            ALTER TABLE users
            ADD COLUMN IF NOT EXISTS is_org_admin BOOLEAN
            """
        )
    )

    # ------------------------------------------------------------------
    # 9. Remove legacy column
    # ------------------------------------------------------------------
    conn.execute(
        sa.text(
            """
            ALTER TABLE users
            DROP COLUMN IF EXISTS is_verified
            """
        )
    )
    

def downgrade() -> None:
    conn = op.get_bind()

    
    for table in reversed(TABLES):
        conn.execute(
            sa.text(
                f"""
                ALTER TABLE {table}
                DROP CONSTRAINT IF EXISTS fk_{table}_org
                """
            )
        )

        conn.execute(
            sa.text(
                f"""
                DROP INDEX IF EXISTS ix_{table}_organization_id
                """
            )
        )

        conn.execute(
            sa.text(
                f"""
                ALTER TABLE {table}
                DROP COLUMN IF EXISTS organization_id
                """
            )
        )

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

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

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

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

    conn.execute(
        sa.text(
            """
            ALTER TABLE users
            ADD COLUMN IF NOT EXISTS is_verified BOOLEAN
            """
        )
    )

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