"""phase54 — link related and duplicate issues

Creates `issue_links`: one row per connection between two issues, displayed on
both of them. See the IssueLink docstring in app/models/issue.py for why the
direction is stored once rather than mirrored.

Purely additive. Nothing reads the table until a person creates a link, so an
empty table is exactly today's behaviour and there is nothing to backfill.

Both FKs use ON DELETE CASCADE so deleting an issue takes its links with it from
either end — a surviving link pointing at a deleted issue would render a dead
row on the other issue's page. The ORM cascade on Issue.links_from/links_to
covers the application path; this covers a direct SQL delete.

Table-existence check — safe to re-run.

Revision ID: phase54_issue_links
Revises: phase53_followup_assignee
"""
from alembic import op
import sqlalchemy as sa

# Revision ids must be <= 32 chars — alembic_version.version_num is VARCHAR(32).
revision      = 'phase54_issue_links'
down_revision = 'phase53_followup_assignee'
branch_labels = None
depends_on    = None


def _table_exists(conn, name):
    return conn.execute(sa.text("""
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t
    """), {'t': name}).scalar() > 0


def upgrade():
    conn = op.get_bind()
    if _table_exists(conn, 'issue_links'):
        return

    op.execute(sa.text("""
        CREATE TABLE issue_links (
            id              INT AUTO_INCREMENT PRIMARY KEY,
            issue_id        INT NOT NULL,
            linked_issue_id INT NOT NULL,
            link_type       ENUM('duplicate','related') NOT NULL DEFAULT 'related',
            created_by      INT NULL,
            created_at      DATETIME NOT NULL,

            CONSTRAINT uq_issue_link UNIQUE (issue_id, linked_issue_id),

            -- Names match what SQLAlchemy's index=True generates, so the
            -- schema this migration builds and the one db.create_all() builds
            -- are identical down to the index names.
            INDEX ix_issue_links_issue_id        (issue_id),
            INDEX ix_issue_links_linked_issue_id (linked_issue_id),

            CONSTRAINT fk_issue_links_issue
                FOREIGN KEY (issue_id) REFERENCES issues (id) ON DELETE CASCADE,
            CONSTRAINT fk_issue_links_linked
                FOREIGN KEY (linked_issue_id) REFERENCES issues (id) ON DELETE CASCADE,
            CONSTRAINT fk_issue_links_creator
                FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """))


def downgrade():
    conn = op.get_bind()
    if _table_exists(conn, 'issue_links'):
        op.execute(sa.text('DROP TABLE issue_links'))