"""direction loop: materials, options, reviews, aspect votes, final picks

Revision ID: 0002_direction_loop
Revises: 0001_baseline
Create Date: 2026-08-11
"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "0002_direction_loop"
down_revision: str | None = "0001_baseline"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column(
        "projects",
        sa.Column("client_website", sa.String(500), nullable=False, server_default=""),
    )

    op.create_table(
        "materials",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "project_id",
            sa.Integer(),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("kind", sa.String(16), nullable=False),
        sa.Column("title", sa.String(200), nullable=False, server_default=""),
        sa.Column("body", sa.Text(), nullable=False, server_default=""),
        sa.Column("url", sa.String(500), nullable=False, server_default=""),
        sa.Column("file_path", sa.String(300), nullable=False, server_default=""),
        sa.Column("file_name", sa.String(255), nullable=False, server_default=""),
        sa.Column("mime", sa.String(120), nullable=False, server_default=""),
        sa.Column("size", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("added_by", sa.String(64), nullable=False),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )
    op.create_index("ix_materials_project_id", "materials", ["project_id"])

    op.create_table(
        "options",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "project_id",
            sa.Integer(),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("slug", sa.String(80), nullable=False),
        sa.Column("display_label", sa.String(40), nullable=False),
        sa.Column("descriptor", sa.String(160), nullable=False, server_default=""),
        sa.Column("source_name", sa.String(200), nullable=False, server_default=""),
        sa.Column("source_url", sa.String(500), nullable=False, server_default=""),
        sa.Column("why_selected", sa.Text(), nullable=False, server_default=""),
        sa.Column("design_notes", sa.Text(), nullable=False, server_default=""),
        sa.Column("reveal_source", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("status", sa.String(16), nullable=False, server_default="draft"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("screenshot_desktop", sa.String(300), nullable=False, server_default=""),
        sa.Column("screenshot_mobile", sa.String(300), nullable=False, server_default=""),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
        sa.UniqueConstraint("project_id", "slug", name="uq_options_project_slug"),
    )
    op.create_index("ix_options_project_id", "options", ["project_id"])

    op.create_table(
        "reviews",
        sa.Column(
            "option_id",
            sa.Integer(),
            sa.ForeignKey("options.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "username",
            sa.String(64),
            sa.ForeignKey("app_accounts.username", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "project_id",
            sa.Integer(),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("rating", sa.Integer(), nullable=True),
        sa.Column("note", sa.Text(), nullable=False, server_default=""),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )
    op.create_index("ix_reviews_project_id", "reviews", ["project_id"])

    op.create_table(
        "aspect_votes",
        sa.Column(
            "option_id",
            sa.Integer(),
            sa.ForeignKey("options.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "username",
            sa.String(64),
            sa.ForeignKey("app_accounts.username", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("aspect", sa.String(32), primary_key=True),
        sa.Column("vote", sa.Integer(), nullable=False),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )

    op.create_table(
        "final_picks",
        sa.Column(
            "project_id",
            sa.Integer(),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "username",
            sa.String(64),
            sa.ForeignKey("app_accounts.username", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "option_id",
            sa.Integer(),
            sa.ForeignKey("options.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column("closing_note", sa.Text(), nullable=False, server_default=""),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
    )


def downgrade() -> None:
    op.drop_table("final_picks")
    op.drop_table("aspect_votes")
    op.drop_index("ix_reviews_project_id", table_name="reviews")
    op.drop_table("reviews")
    op.drop_index("ix_options_project_id", table_name="options")
    op.drop_table("options")
    op.drop_index("ix_materials_project_id", table_name="materials")
    op.drop_table("materials")
    op.drop_column("projects", "client_website")