"""Add CMS pages, blog posts, platform settings, email templates, plot_types.sections

Revision ID: 0013
Revises: 0012
Create Date: 2026-06-25 00:00:00.000000
"""
from typing import Sequence, Union
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0013"
down_revision: Union[str, None] = "0012"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ── plot_types: add sections column ─────────────────────────────────────
    op.add_column("plot_types", sa.Column("sections", sa.String(255), nullable=True))

    # ── email_templates ──────────────────────────────────────────────────────
    op.create_table(
        "email_templates",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True,
                  server_default=sa.text("gen_random_uuid()")),
        sa.Column("tenant_id", postgresql.UUID(as_uuid=True),
                  sa.ForeignKey("accounts.id", ondelete="CASCADE"), nullable=False),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column("trigger", sa.String(255), nullable=True),
        sa.Column("subject", sa.String(500), nullable=False, server_default=""),
        sa.Column("body", sa.Text, nullable=False, server_default=""),
        sa.Column("is_enabled", sa.Boolean, nullable=False, server_default="true"),
        sa.Column("variables", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_email_templates_tenant_id", "email_templates", ["tenant_id"])

    # ── cms_pages ────────────────────────────────────────────────────────────
    op.create_table(
        "cms_pages",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True,
                  server_default=sa.text("gen_random_uuid()")),
        sa.Column("name", sa.String(100), nullable=False),
        sa.Column("path", sa.String(255), nullable=False, unique=True),
        sa.Column("headline", sa.String(500), nullable=True),
        sa.Column("subhead", sa.String(500), nullable=True),
        sa.Column("seo_title", sa.String(255), nullable=True),
        sa.Column("seo_description", sa.Text, nullable=True),
        sa.Column("status", sa.String(20), nullable=False, server_default="Draft"),
        sa.Column("created_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_cms_pages_path", "cms_pages", ["path"], unique=True)

    # ── blog_posts ───────────────────────────────────────────────────────────
    op.create_table(
        "blog_posts",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True,
                  server_default=sa.text("gen_random_uuid()")),
        sa.Column("title", sa.String(500), nullable=False),
        sa.Column("category", sa.String(100), nullable=True),
        sa.Column("excerpt", sa.Text, nullable=True),
        sa.Column("body", sa.Text, nullable=True),
        sa.Column("status", sa.String(20), nullable=False, server_default="Draft"),
        sa.Column("published_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_blog_posts_status", "blog_posts", ["status"])

    # ── platform_settings ────────────────────────────────────────────────────
    op.create_table(
        "platform_settings",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True,
                  server_default=sa.text("gen_random_uuid()")),
        sa.Column("email", sa.String(255), nullable=True),
        sa.Column("phone", sa.String(50), nullable=True),
        sa.Column("office", sa.String(255), nullable=True),
        sa.Column("hours", sa.String(255), nullable=True),
        sa.Column("linkedin", sa.String(500), nullable=True),
        sa.Column("twitter", sa.String(500), nullable=True),
        sa.Column("meta_title", sa.String(255), nullable=True),
        sa.Column("meta_description", sa.Text, nullable=True),
        sa.Column("announcement", sa.Text, nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True),
                  server_default=sa.text("now()"), nullable=False),
    )


def downgrade() -> None:
    op.drop_table("platform_settings")
    op.drop_index("ix_blog_posts_status", table_name="blog_posts")
    op.drop_table("blog_posts")
    op.drop_index("ix_cms_pages_path", table_name="cms_pages")
    op.drop_table("cms_pages")
    op.drop_index("ix_email_templates_tenant_id", table_name="email_templates")
    op.drop_table("email_templates")
    op.drop_column("plot_types", "sections")