"""add contacts and emails to vendor

Revision ID: 4e6a08fb4b17
Revises: d59bf49563cd
Create Date: 2026-07-02 16:22:32.572473

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
"""add contacts and emails to vendor

Revision ID: 4e6a08fb4b17
Revises: d59bf49563cd
Create Date: 2026-07-02 16:22:32.572473

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = '4e6a08fb4b17'
down_revision: Union[str, None] = 'd59bf49563cd'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('vendor_master', sa.Column('contacts', postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default='[]'))
    op.add_column('vendor_master', sa.Column('emails', postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default='[]'))
    
    # Migrate existing data
    op.execute(
        """
        UPDATE vendor_master
        SET contacts = json_build_array(
            json_build_object(
                'name', COALESCE(contact_person, ''),
                'phone', COALESCE(phone, '')
            )
        )
        WHERE contact_person IS NOT NULL OR phone IS NOT NULL
        """
    )
    op.execute(
        """
        UPDATE vendor_master
        SET emails = json_build_array(email)
        WHERE email IS NOT NULL
        """
    )
    
    op.drop_column('vendor_master', 'contact_person')
    op.drop_column('vendor_master', 'phone')
    op.drop_column('vendor_master', 'email')
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('vendor_master', sa.Column('email', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
    op.add_column('vendor_master', sa.Column('phone', sa.VARCHAR(length=20), autoincrement=False, nullable=True))
    op.add_column('vendor_master', sa.Column('contact_person', sa.VARCHAR(length=150), autoincrement=False, nullable=True))
    
    # Restore from JSONB
    op.execute(
        """
        UPDATE vendor_master
        SET contact_person = contacts->0->>'name',
            phone = contacts->0->>'phone'
        WHERE jsonb_array_length(contacts) > 0
        """
    )
    op.execute(
        """
        UPDATE vendor_master
        SET email = emails->>0
        WHERE jsonb_array_length(emails) > 0
        """
    )
    
    op.drop_column('vendor_master', 'emails')
    op.drop_column('vendor_master', 'contacts')
    # ### end Alembic commands ###