Files
saura_shakti_website/backend/alembic/versions/20261113_d572dd3db977_initial_schema.py
Shubhankar45 53dbfa976b -
2026-11-13 12:14:55 +00:00

193 lines
13 KiB
Python

"""initial schema
Revision ID: d572dd3db977
Revises:
Create Date: 2026-11-13 06:46:38.545847
"""
from typing import Sequence, Union
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql
revision: str = 'd572dd3db977'
down_revision: Union[str, None] = None
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.create_table('admins',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(length=255), nullable=False),
sa.Column('full_name', sa.String(length=150), nullable=False),
sa.Column('hashed_password', sa.String(length=255), nullable=False),
sa.Column('role', sa.Enum('super_admin', 'admin', 'editor', name='admin_role', native_enum=False, create_constraint=True, length=32), server_default='admin', nullable=False),
sa.Column('is_active', sa.Boolean(), server_default=sa.text('true'), nullable=False),
sa.Column('last_login_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),
sa.PrimaryKeyConstraint('id', name=op.f('pk_admins'))
)
op.create_index(op.f('ix_admins_email'), 'admins', ['email'], unique=True)
op.create_table('contact_messages',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('email', sa.String(length=255), nullable=False),
sa.Column('phone', sa.String(length=20), nullable=True),
sa.Column('subject', sa.String(length=200), nullable=True),
sa.Column('message', sa.Text(), nullable=False),
sa.Column('is_read', sa.Boolean(), server_default=sa.text('false'), nullable=False),
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),
sa.PrimaryKeyConstraint('id', name=op.f('pk_contact_messages'))
)
op.create_index(op.f('ix_contact_messages_email'), 'contact_messages', ['email'], unique=False)
op.create_index(op.f('ix_contact_messages_is_read'), 'contact_messages', ['is_read'], unique=False)
op.create_table('enquiries',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=150), nullable=False),
sa.Column('phone', sa.String(length=20), nullable=False),
sa.Column('email', sa.String(length=255), nullable=True),
sa.Column('city', sa.String(length=120), nullable=True),
sa.Column('address', sa.String(length=500), nullable=True),
sa.Column('property_type', sa.Enum('residential', 'commercial', 'industrial', 'other', name='property_type', native_enum=False, create_constraint=True, length=32), nullable=True),
sa.Column('monthly_bill', sa.Numeric(precision=12, scale=2), nullable=True),
sa.Column('required_capacity_kw', sa.Numeric(precision=10, scale=2), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('source', sa.String(length=50), server_default='website', nullable=False),
sa.Column('status', sa.Enum('new', 'contacted', 'site_visit_scheduled', 'quotation_sent', 'converted', 'rejected', name='enquiry_status', native_enum=False, create_constraint=True, length=32), server_default='new', nullable=False),
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),
sa.CheckConstraint('monthly_bill IS NULL OR monthly_bill >= 0', name=op.f('ck_enquiries_monthly_bill_non_negative')),
sa.CheckConstraint('required_capacity_kw IS NULL OR required_capacity_kw > 0', name=op.f('ck_enquiries_capacity_positive')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_enquiries'))
)
op.create_index(op.f('ix_enquiries_email'), 'enquiries', ['email'], unique=False)
op.create_index(op.f('ix_enquiries_phone'), 'enquiries', ['phone'], unique=False)
op.create_index('ix_enquiries_status_created_at', 'enquiries', ['status', 'created_at'], unique=False)
op.create_table('projects',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=200), nullable=False),
sa.Column('slug', sa.String(length=220), nullable=False),
sa.Column('category', sa.Enum('residential', 'commercial', name='project_category', native_enum=False, create_constraint=True, length=32), nullable=False),
sa.Column('location', sa.String(length=200), nullable=True),
sa.Column('capacity_kw', sa.Numeric(precision=10, scale=2), nullable=True),
sa.Column('installation_date', sa.Date(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('status', sa.Enum('ongoing', 'completed', name='project_status', native_enum=False, create_constraint=True, length=32), server_default='completed', nullable=False),
sa.Column('is_published', sa.Boolean(), server_default=sa.text('false'), nullable=False),
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),
sa.CheckConstraint('capacity_kw IS NULL OR capacity_kw > 0', name=op.f('ck_projects_capacity_positive')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_projects'))
)
op.create_index('ix_projects_published_category', 'projects', ['is_published', 'category'], unique=False)
op.create_index(op.f('ix_projects_slug'), 'projects', ['slug'], unique=True)
op.create_table('services',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=150), nullable=False),
sa.Column('slug', sa.String(length=170), nullable=False),
sa.Column('short_description', sa.String(length=300), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('icon', sa.String(length=60), nullable=True),
sa.Column('sort_order', sa.Integer(), server_default='0', nullable=False),
sa.Column('is_published', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.Column('image_original_path', sa.String(length=300), nullable=True),
sa.Column('image_watermarked_path', sa.String(length=300), nullable=True),
sa.Column('image_thumbnail_path', sa.String(length=300), 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),
sa.PrimaryKeyConstraint('id', name=op.f('pk_services'))
)
op.create_index(op.f('ix_services_slug'), 'services', ['slug'], unique=True)
op.create_table('enquiry_notes',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('enquiry_id', sa.Integer(), nullable=False),
sa.Column('author_id', sa.Integer(), nullable=True),
sa.Column('content', sa.Text(), nullable=False),
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),
sa.ForeignKeyConstraint(['author_id'], ['admins.id'], name=op.f('fk_enquiry_notes_author_id_admins'), ondelete='SET NULL'),
sa.ForeignKeyConstraint(['enquiry_id'], ['enquiries.id'], name=op.f('fk_enquiry_notes_enquiry_id_enquiries'), ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id', name=op.f('pk_enquiry_notes'))
)
op.create_index(op.f('ix_enquiry_notes_enquiry_id'), 'enquiry_notes', ['enquiry_id'], unique=False)
op.create_table('project_images',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('project_id', sa.Integer(), nullable=False),
sa.Column('original_filename', sa.String(length=255), nullable=False),
sa.Column('original_path', sa.String(length=300), nullable=False),
sa.Column('watermarked_path', sa.String(length=300), nullable=False),
sa.Column('thumbnail_path', sa.String(length=300), nullable=False),
sa.Column('mime_type', sa.String(length=50), nullable=False),
sa.Column('file_size', sa.Integer(), nullable=False),
sa.Column('width', sa.Integer(), nullable=False),
sa.Column('height', sa.Integer(), nullable=False),
sa.Column('caption', sa.String(length=300), nullable=True),
sa.Column('sort_order', sa.Integer(), server_default='0', nullable=False),
sa.Column('is_featured', sa.Boolean(), server_default=sa.text('false'), nullable=False),
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),
sa.CheckConstraint('file_size > 0', name=op.f('ck_project_images_file_size_positive')),
sa.ForeignKeyConstraint(['project_id'], ['projects.id'], name=op.f('fk_project_images_project_id_projects'), ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id', name=op.f('pk_project_images'))
)
op.create_index(op.f('ix_project_images_project_id'), 'project_images', ['project_id'], unique=False)
op.create_index('uq_project_images_one_featured', 'project_images', ['project_id'], unique=True, postgresql_where=sa.text('is_featured'))
op.create_table('site_content',
sa.Column('key', sa.String(length=80), nullable=False),
sa.Column('value', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
sa.Column('updated_by_id', sa.Integer(), 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),
sa.ForeignKeyConstraint(['updated_by_id'], ['admins.id'], name=op.f('fk_site_content_updated_by_id_admins'), ondelete='SET NULL'),
sa.PrimaryKeyConstraint('key', name=op.f('pk_site_content'))
)
op.create_table('testimonials',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('customer_name', sa.String(length=150), nullable=False),
sa.Column('designation', sa.String(length=150), nullable=True),
sa.Column('location', sa.String(length=150), nullable=True),
sa.Column('content', sa.Text(), nullable=False),
sa.Column('rating', sa.SmallInteger(), server_default='5', nullable=False),
sa.Column('project_id', sa.Integer(), nullable=True),
sa.Column('sort_order', sa.Integer(), server_default='0', nullable=False),
sa.Column('is_published', sa.Boolean(), server_default=sa.text('true'), nullable=False),
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),
sa.CheckConstraint('rating BETWEEN 1 AND 5', name=op.f('ck_testimonials_rating_range')),
sa.ForeignKeyConstraint(['project_id'], ['projects.id'], name=op.f('fk_testimonials_project_id_projects'), ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id', name=op.f('pk_testimonials'))
)
op.create_index(op.f('ix_testimonials_project_id'), 'testimonials', ['project_id'], unique=False)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index(op.f('ix_testimonials_project_id'), table_name='testimonials')
op.drop_table('testimonials')
op.drop_table('site_content')
op.drop_index('uq_project_images_one_featured', table_name='project_images', postgresql_where=sa.text('is_featured'))
op.drop_index(op.f('ix_project_images_project_id'), table_name='project_images')
op.drop_table('project_images')
op.drop_index(op.f('ix_enquiry_notes_enquiry_id'), table_name='enquiry_notes')
op.drop_table('enquiry_notes')
op.drop_index(op.f('ix_services_slug'), table_name='services')
op.drop_table('services')
op.drop_index(op.f('ix_projects_slug'), table_name='projects')
op.drop_index('ix_projects_published_category', table_name='projects')
op.drop_table('projects')
op.drop_index('ix_enquiries_status_created_at', table_name='enquiries')
op.drop_index(op.f('ix_enquiries_phone'), table_name='enquiries')
op.drop_index(op.f('ix_enquiries_email'), table_name='enquiries')
op.drop_table('enquiries')
op.drop_index(op.f('ix_contact_messages_is_read'), table_name='contact_messages')
op.drop_index(op.f('ix_contact_messages_email'), table_name='contact_messages')
op.drop_table('contact_messages')
op.drop_index(op.f('ix_admins_email'), table_name='admins')
op.drop_table('admins')
# ### end Alembic commands ###