"""Project Storage Buckets: project_storage_buckets and project_storage_usage tables.

Revision ID: 035
Revises: 034
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "035"
down_revision = "034"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # project_storage_buckets — named bucket definitions per project
    # access_policy is stored as plain text (not enum) for forward-compatibility
    op.create_table(
        "project_storage_buckets",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("name", sa.String(64), nullable=False),             # slug, e.g. "avatars"
        # access_policy: public | private | authenticated
        sa.Column("access_policy", sa.String(20), nullable=False, server_default="private"),
        sa.Column("rls_enabled", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("cdn_enabled", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("cdn_custom_domain", sa.Text, nullable=True),
        # null = all mime types allowed
        sa.Column("allowed_mime_types_json", postgresql.JSONB, nullable=True),
        # null = plan default
        sa.Column("max_file_size_bytes", sa.BigInteger, nullable=True),
        sa.Column("is_active", sa.Boolean, nullable=False, server_default="true"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("project_id", "name", name="uq_project_storage_bucket_project_name"),
    )
    op.create_index("ix_project_storage_buckets_project_id", "project_storage_buckets", ["project_id"])

    # project_storage_usage — running counter per project (updated on every upload/delete)
    # Used for quota enforcement at presign time and for the dashboard usage display
    op.create_table(
        "project_storage_usage",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),
        sa.Column("total_bytes", sa.BigInteger, nullable=False, server_default="0"),
        sa.Column("total_files", sa.Integer, nullable=False, server_default="0"),
        sa.Column("last_updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )
    op.create_index(
        "ix_project_storage_usage_project_id", "project_storage_usage", ["project_id"], unique=True
    )


def downgrade() -> None:
    op.drop_table("project_storage_usage")
    op.drop_table("project_storage_buckets")