"""Initial migration: add file_paths, file_metadata, and content_chunks tables

Revision ID: 58d58578fce0
Revises:
Create Date: 2025-10-13 21:58:36.566590

"""

from collections.abc import Sequence

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "58d58578fce0"
down_revision: str | Sequence[str] | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "content_chunks",
        sa.Column("chunk_id", sa.String(length=36), nullable=False),
        sa.Column("content_hash", sa.String(length=64), nullable=False),
        sa.Column("size_bytes", sa.BigInteger(), nullable=False),
        sa.Column("storage_path", sa.Text(), nullable=False),
        sa.Column("ref_count", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("last_accessed_at", sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint("chunk_id"),
        sa.UniqueConstraint("content_hash"),
    )
    op.create_index("idx_content_chunks_hash", "content_chunks", ["content_hash"], unique=False)
    op.create_index("idx_content_chunks_ref_count", "content_chunks", ["ref_count"], unique=False)
    op.create_table(
        "file_paths",
        sa.Column("path_id", sa.String(length=36), nullable=False),
        sa.Column("tenant_id", sa.String(length=36), nullable=False),
        sa.Column("virtual_path", sa.Text(), nullable=False),
        sa.Column("backend_id", sa.String(length=36), nullable=False),
        sa.Column("physical_path", sa.Text(), nullable=False),
        sa.Column("file_type", sa.String(length=50), nullable=True),
        sa.Column("size_bytes", sa.BigInteger(), nullable=False),
        sa.Column("content_hash", sa.String(length=64), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("deleted_at", sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint("path_id"),
        sa.UniqueConstraint("tenant_id", "virtual_path", name="uq_tenant_virtual_path"),
    )
    op.create_index("idx_file_paths_backend_id", "file_paths", ["backend_id"], unique=False)
    op.create_index("idx_file_paths_content_hash", "file_paths", ["content_hash"], unique=False)
    op.create_index("idx_file_paths_tenant_id", "file_paths", ["tenant_id"], unique=False)
    op.create_index("idx_file_paths_virtual_path", "file_paths", ["virtual_path"], unique=False)
    op.create_table(
        "file_metadata",
        sa.Column("metadata_id", sa.String(length=36), nullable=False),
        sa.Column("path_id", sa.String(length=36), nullable=False),
        sa.Column("key", sa.String(length=255), nullable=False),
        sa.Column("value", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["path_id"], ["file_paths.path_id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("metadata_id"),
    )
    op.create_index("idx_file_metadata_key", "file_metadata", ["key"], unique=False)
    op.create_index("idx_file_metadata_path_id", "file_metadata", ["path_id"], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index("idx_file_metadata_path_id", table_name="file_metadata")
    op.drop_index("idx_file_metadata_key", table_name="file_metadata")
    op.drop_table("file_metadata")
    op.drop_index("idx_file_paths_virtual_path", table_name="file_paths")
    op.drop_index("idx_file_paths_tenant_id", table_name="file_paths")
    op.drop_index("idx_file_paths_content_hash", table_name="file_paths")
    op.drop_index("idx_file_paths_backend_id", table_name="file_paths")
    op.drop_table("file_paths")
    op.drop_index("idx_content_chunks_ref_count", table_name="content_chunks")
    op.drop_index("idx_content_chunks_hash", table_name="content_chunks")
    op.drop_table("content_chunks")
    # ### end Alembic commands ###
