"""add url_safety_checks, customer_sites, site_credentials tables

Revision ID: z7a8b9c0d1e2
Revises: y6z7a8b9c0d1
Create Date: 2026-04-14
"""
from alembic import op
import sqlalchemy as sa

revision = 'z7a8b9c0d1e2'
down_revision = 'y6z7a8b9c0d1'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'url_safety_checks',
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
        sa.Column('url_hash', sa.String(64), unique=True, nullable=False, index=True),
        sa.Column('url', sa.Text, nullable=False),
        sa.Column('detections', sa.Integer, nullable=False, default=0),
        sa.Column('total_scanners', sa.Integer, nullable=False, default=0),
        sa.Column('status', sa.String(20), nullable=False, default='unknown'),
        sa.Column('checked_at', sa.DateTime, nullable=False),
    )

    op.create_table(
        'customer_sites',
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
        sa.Column('customer_hash', sa.String(255), nullable=False, index=True),
        sa.Column('label', sa.String(255), nullable=False),
        sa.Column('url', sa.String(500), nullable=True),
        sa.Column('notes', sa.Text, nullable=True),
        sa.Column('created_at', sa.DateTime, nullable=False),
        sa.Column('updated_at', sa.DateTime, nullable=False),
    )

    op.create_table(
        'site_credentials',
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
        sa.Column('site_id', sa.Integer, sa.ForeignKey('customer_sites.id'), nullable=False),
        sa.Column('type', sa.String(50), nullable=False, server_default='backoffice'),
        sa.Column('label', sa.String(255), nullable=True),
        sa.Column('username', sa.String(255), nullable=True),
        sa.Column('password_encrypted', sa.Text, nullable=True),
        sa.Column('extra_json', sa.Text, nullable=True),
        sa.Column('created_at', sa.DateTime, nullable=False),
        sa.Column('updated_at', sa.DateTime, nullable=False),
    )


def downgrade():
    op.drop_table('site_credentials')
    op.drop_table('customer_sites')
    op.drop_table('url_safety_checks')