"""
Alembic Migration Script - Add Workflow Tables

Run with:
    alembic revision --autogenerate -m "Add workflow tables"
    alembic upgrade head

Or use this standalone script:
    python migrations/create_workflow_tables.py
"""
import sys
import uuid
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent))

from sqlalchemy import text
from app.core.database import engine


def create_workflow_tables():
    """Create all workflow-related tables"""

    with engine.connect() as conn:
        # Enable UUID extension if not exists
        conn.execute(text("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"))

        # Create workflow_definitions table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS workflow_definitions (
                id VARCHAR PRIMARY KEY DEFAULT uuid_generate_v4()::text,
                name VARCHAR NOT NULL,
                code VARCHAR UNIQUE NOT NULL,
                description TEXT,
                module VARCHAR NOT NULL,
                is_active BOOLEAN DEFAULT TRUE NOT NULL,
                is_default BOOLEAN DEFAULT FALSE,
                requires_approval BOOLEAN DEFAULT TRUE,
                auto_submit BOOLEAN DEFAULT FALSE,
                approval_threshold_amount INTEGER DEFAULT 0,
                max_approval_levels INTEGER DEFAULT 3,
                created_by VARCHAR REFERENCES users(id),
                branch_id VARCHAR REFERENCES branches(id),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_workflow_def_module ON workflow_definitions(module);
            CREATE INDEX IF NOT EXISTS idx_workflow_def_code ON workflow_definitions(code);
        """))

        # Create workflow_states table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS workflow_states (
                id VARCHAR PRIMARY KEY DEFAULT uuid_generate_v4()::text,
                workflow_definition_id VARCHAR NOT NULL REFERENCES workflow_definitions(id) ON DELETE CASCADE,
                name VARCHAR NOT NULL,
                code VARCHAR NOT NULL,
                status VARCHAR NOT NULL,
                description TEXT,
                is_initial BOOLEAN DEFAULT FALSE,
                is_final BOOLEAN DEFAULT FALSE,
                requires_approval BOOLEAN DEFAULT FALSE,
                allowed_roles JSON,
                notified_roles JSON,
                display_order INTEGER DEFAULT 0,
                color VARCHAR DEFAULT '#3b82f6',
                icon VARCHAR DEFAULT 'circle',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_workflow_state_def ON workflow_states(workflow_definition_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_state_code ON workflow_states(code);
        """))

        # Create workflow_transitions table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS workflow_transitions (
                id VARCHAR PRIMARY KEY DEFAULT uuid_generate_v4()::text,
                workflow_definition_id VARCHAR NOT NULL REFERENCES workflow_definitions(id) ON DELETE CASCADE,
                from_state_id VARCHAR NOT NULL REFERENCES workflow_states(id) ON DELETE CASCADE,
                to_state_id VARCHAR NOT NULL REFERENCES workflow_states(id) ON DELETE CASCADE,
                name VARCHAR NOT NULL,
                action VARCHAR NOT NULL,
                description TEXT,
                allowed_roles JSON,
                required_permission VARCHAR,
                requires_comment BOOLEAN DEFAULT FALSE,
                requires_attachment BOOLEAN DEFAULT FALSE,
                condition_script TEXT,
                notify_on_transition BOOLEAN DEFAULT TRUE,
                notification_template VARCHAR,
                button_label VARCHAR,
                button_color VARCHAR DEFAULT 'primary',
                display_order INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_workflow_trans_def ON workflow_transitions(workflow_definition_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_trans_from ON workflow_transitions(from_state_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_trans_to ON workflow_transitions(to_state_id);
        """))

        # Create workflow_instances table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS workflow_instances (
                id VARCHAR PRIMARY KEY DEFAULT uuid_generate_v4()::text,
                workflow_definition_id VARCHAR NOT NULL REFERENCES workflow_definitions(id),
                current_state_id VARCHAR NOT NULL REFERENCES workflow_states(id),
                entity_type VARCHAR NOT NULL,
                entity_id VARCHAR NOT NULL,
                status VARCHAR NOT NULL,
                initiated_by VARCHAR NOT NULL REFERENCES users(id),
                initiated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                current_assignee VARCHAR REFERENCES users(id),
                completed_at TIMESTAMP,
                completed_by VARCHAR REFERENCES users(id),
                branch_id VARCHAR REFERENCES branches(id),
                priority VARCHAR DEFAULT 'normal',
                due_date TIMESTAMP,
                metadata JSON,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_workflow_inst_entity ON workflow_instances(entity_type, entity_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_inst_status ON workflow_instances(status);
            CREATE INDEX IF NOT EXISTS idx_workflow_inst_assignee ON workflow_instances(current_assignee);
            CREATE INDEX IF NOT EXISTS idx_workflow_inst_state ON workflow_instances(current_state_id);
        """))

        # Create workflow_actions table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS workflow_actions (
                id VARCHAR PRIMARY KEY DEFAULT uuid_generate_v4()::text,
                workflow_instance_id VARCHAR NOT NULL REFERENCES workflow_instances(id) ON DELETE CASCADE,
                from_state_id VARCHAR REFERENCES workflow_states(id),
                to_state_id VARCHAR NOT NULL REFERENCES workflow_states(id),
                action VARCHAR NOT NULL,
                action_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                performed_by VARCHAR NOT NULL REFERENCES users(id),
                comment TEXT,
                reason VARCHAR,
                attachments JSON,
                reassigned_from VARCHAR REFERENCES users(id),
                reassigned_to VARCHAR REFERENCES users(id),
                ip_address VARCHAR,
                user_agent VARCHAR,
                duration_seconds INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_workflow_action_inst ON workflow_actions(workflow_instance_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_action_date ON workflow_actions(action_date);
            CREATE INDEX IF NOT EXISTS idx_workflow_action_to_state ON workflow_actions(to_state_id);
        """))

        # Create workflow_notifications table
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS workflow_notifications (
                id VARCHAR PRIMARY KEY DEFAULT uuid_generate_v4()::text,
                workflow_instance_id VARCHAR NOT NULL REFERENCES workflow_instances(id) ON DELETE CASCADE,
                workflow_action_id VARCHAR REFERENCES workflow_actions(id),
                recipient_user_id VARCHAR NOT NULL REFERENCES users(id),
                notification_type VARCHAR NOT NULL,
                subject VARCHAR,
                message TEXT,
                sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                read_at TIMESTAMP,
                is_read BOOLEAN DEFAULT FALSE,
                delivery_status VARCHAR DEFAULT 'sent',
                error_message TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE INDEX IF NOT EXISTS idx_workflow_notif_inst ON workflow_notifications(workflow_instance_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_notif_recipient ON workflow_notifications(recipient_user_id);
            CREATE INDEX IF NOT EXISTS idx_workflow_notif_read ON workflow_notifications(is_read);
        """))

        _ensure_workflow_id_defaults(conn)
        conn.commit()
        print("Workflow tables created successfully.")

        # Create sample workflows
        create_sample_workflows(conn)


def _ensure_workflow_id_defaults(conn):
    """Backfill id and timestamp defaults on tables created before DEFAULT was applied."""
    tables_with_timestamps = (
        "workflow_definitions",
        "workflow_states",
        "workflow_transitions",
        "workflow_instances",
        "workflow_actions",
        "workflow_notifications",
    )
    for table in tables_with_timestamps:
        conn.execute(text(f"""
            ALTER TABLE {table}
            ALTER COLUMN id SET DEFAULT uuid_generate_v4()::text
        """))
        conn.execute(text(f"""
            ALTER TABLE {table}
            ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP
        """))
        conn.execute(text(f"""
            ALTER TABLE {table}
            ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP
        """))


def create_sample_workflows(conn):
    """Create sample workflow definitions"""

    print("\n📋 Creating sample workflows...")

    # Check if sample workflows already exist
    result = conn.execute(text(
        "SELECT COUNT(*) FROM workflow_definitions WHERE code IN ('PO_APPROVAL_3LEVEL', 'SALES_APPROVAL_2LEVEL')"
    ))
    existing_count = result.scalar()

    if existing_count > 0:
        print(f"⚠️  Sample workflows already exist (found {existing_count}). Skipping creation.")
        return

    # Create Purchase Order Approval Workflow
    print("\n[1/2] Creating Purchase Order Approval (3-Level) workflow...")

    po_workflow_id = str(uuid.uuid4())
    po_states = {
        "DRAFT": str(uuid.uuid4()),
        "PENDING_MGR": str(uuid.uuid4()),
        "PENDING_FIN": str(uuid.uuid4()),
        "APPROVED": str(uuid.uuid4()),
        "REJECTED": str(uuid.uuid4()),
    }

    conn.execute(
        text("""
            INSERT INTO workflow_definitions
                (id, name, code, module, description, is_active, is_default, max_approval_levels, created_at, updated_at)
            VALUES
                (:id, 'Purchase Order Approval (3-Level)', 'PO_APPROVAL_3LEVEL', 'purchases',
                 'Standard 3-level approval: Submitter -> Manager -> Finance Director', TRUE, TRUE, 3,
                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """),
        {"id": po_workflow_id},
    )

    conn.execute(
        text("""
            INSERT INTO workflow_states
                (id, workflow_definition_id, name, code, status, is_initial, is_final, display_order, color, icon, created_at, updated_at)
            VALUES
                (:draft_id, :wf_id, 'Draft', 'DRAFT', 'DRAFT', TRUE, FALSE, 1, '#6b7280', 'file-earmark', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:pending_mgr_id, :wf_id, 'Pending Manager Approval', 'PENDING_MGR', 'PENDING_APPROVAL', FALSE, FALSE, 2, '#f59e0b', 'clock', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:pending_fin_id, :wf_id, 'Pending Finance Approval', 'PENDING_FIN', 'PENDING_APPROVAL', FALSE, FALSE, 3, '#f59e0b', 'cash-stack', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:approved_id, :wf_id, 'Approved', 'APPROVED', 'APPROVED', FALSE, TRUE, 4, '#10b981', 'check-circle', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:rejected_id, :wf_id, 'Rejected', 'REJECTED', 'REJECTED', FALSE, TRUE, 5, '#ef4444', 'x-circle', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """),
        {
            "wf_id": po_workflow_id,
            "draft_id": po_states["DRAFT"],
            "pending_mgr_id": po_states["PENDING_MGR"],
            "pending_fin_id": po_states["PENDING_FIN"],
            "approved_id": po_states["APPROVED"],
            "rejected_id": po_states["REJECTED"],
        },
    )

    conn.execute(
        text("""
            INSERT INTO workflow_transitions
                (id, workflow_definition_id, from_state_id, to_state_id, name, action, button_label, button_color, display_order, created_at, updated_at)
            VALUES
                (:t1, :wf_id, :draft, :pending_mgr, 'Submit for Approval', 'SUBMIT', 'Submit', 'primary', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:t2, :wf_id, :pending_mgr, :pending_fin, 'Manager Approval', 'APPROVE', 'Approve', 'success', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:t3, :wf_id, :pending_mgr, :rejected, 'Manager Rejection', 'REJECT', 'Reject', 'danger', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:t4, :wf_id, :pending_fin, :approved, 'Finance Approval', 'APPROVE', 'Approve', 'success', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:t5, :wf_id, :pending_fin, :rejected, 'Finance Rejection', 'REJECT', 'Reject', 'danger', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """),
        {
            "wf_id": po_workflow_id,
            "draft": po_states["DRAFT"],
            "pending_mgr": po_states["PENDING_MGR"],
            "pending_fin": po_states["PENDING_FIN"],
            "approved": po_states["APPROVED"],
            "rejected": po_states["REJECTED"],
            "t1": str(uuid.uuid4()),
            "t2": str(uuid.uuid4()),
            "t3": str(uuid.uuid4()),
            "t4": str(uuid.uuid4()),
            "t5": str(uuid.uuid4()),
        },
    )

    print(f"   Created Purchase Order workflow (ID: {po_workflow_id})")

    # Create Sales Invoice Approval Workflow
    print("\n[2/2] Creating Sales Invoice Approval (2-Level) workflow...")

    sales_workflow_id = str(uuid.uuid4())
    sales_states = {
        "DRAFT": str(uuid.uuid4()),
        "PENDING_MGR": str(uuid.uuid4()),
        "APPROVED": str(uuid.uuid4()),
        "REJECTED": str(uuid.uuid4()),
    }

    conn.execute(
        text("""
            INSERT INTO workflow_definitions
                (id, name, code, module, description, is_active, is_default, max_approval_levels, created_at, updated_at)
            VALUES
                (:id, 'Sales Invoice Approval (2-Level)', 'SALES_APPROVAL_2LEVEL', 'sales',
                 'Standard 2-level approval: Salesperson -> Sales Manager', TRUE, TRUE, 2,
                 CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """),
        {"id": sales_workflow_id},
    )

    conn.execute(
        text("""
            INSERT INTO workflow_states
                (id, workflow_definition_id, name, code, status, is_initial, is_final, display_order, color, icon, created_at, updated_at)
            VALUES
                (:draft_id, :wf_id, 'Draft', 'DRAFT', 'DRAFT', TRUE, FALSE, 1, '#6b7280', 'file-earmark', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:pending_mgr_id, :wf_id, 'Pending Sales Manager', 'PENDING_MGR', 'PENDING_APPROVAL', FALSE, FALSE, 2, '#f59e0b', 'clock', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:approved_id, :wf_id, 'Approved', 'APPROVED', 'APPROVED', FALSE, TRUE, 3, '#10b981', 'check-circle', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:rejected_id, :wf_id, 'Rejected', 'REJECTED', 'REJECTED', FALSE, TRUE, 4, '#ef4444', 'x-circle', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """),
        {
            "wf_id": sales_workflow_id,
            "draft_id": sales_states["DRAFT"],
            "pending_mgr_id": sales_states["PENDING_MGR"],
            "approved_id": sales_states["APPROVED"],
            "rejected_id": sales_states["REJECTED"],
        },
    )

    conn.execute(
        text("""
            INSERT INTO workflow_transitions
                (id, workflow_definition_id, from_state_id, to_state_id, name, action, button_label, button_color, display_order, created_at, updated_at)
            VALUES
                (:t1, :wf_id, :draft, :pending_mgr, 'Submit for Approval', 'SUBMIT', 'Submit', 'primary', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:t2, :wf_id, :pending_mgr, :approved, 'Manager Approval', 'APPROVE', 'Approve', 'success', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (:t3, :wf_id, :pending_mgr, :rejected, 'Manager Rejection', 'REJECT', 'Reject', 'danger', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """),
        {
            "wf_id": sales_workflow_id,
            "draft": sales_states["DRAFT"],
            "pending_mgr": sales_states["PENDING_MGR"],
            "approved": sales_states["APPROVED"],
            "rejected": sales_states["REJECTED"],
            "t1": str(uuid.uuid4()),
            "t2": str(uuid.uuid4()),
            "t3": str(uuid.uuid4()),
        },
    )

    print(f"   Created Sales Invoice workflow (ID: {sales_workflow_id})")

    conn.commit()
    print("\n✅ Sample workflows created successfully!")
    print("\n📚 Next steps:")
    print("   1. Configure role assignments for workflow states/transitions")
    print("   2. Integrate workflows into your modules (purchases, sales, etc.)")
    print("   3. View workflows at: http://localhost:8010/static/workflow-components.html")


if __name__ == "__main__":
    print("🚀 Creating workflow tables...")
    print("=" * 60)

    try:
        create_workflow_tables()
        print("\n" + "=" * 60)
        print("✅ Workflow system setup complete!")
        print("\n📖 Documentation:")
        print("   - Full Guide: docs/workflow-system-guide.md")
        print("   - Quick Ref:  docs/workflow-quick-reference.md")
        print("   - UI Demo:    app/static/workflow-components.html")

    except Exception as e:
        print(f"\nError creating workflow tables: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)
