import uuid
from config.get_config import master_config
from sqlalchemy import Column, String, Integer, TIMESTAMP, func
from sqlalchemy.dialects.postgresql import UUID, ARRAY, DOUBLE_PRECISION, BOOLEAN
from sqlalchemy.orm import declarative_base
# from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
# from config.constant import EnvPostgresConstants

# engine = create_async_engine(
#     EnvPostgresConstants.CONSTRING.replace('psycopg2', 'asyncpg'),
#     pool_pre_ping=True,
#     pool_recycle=300,
# )

# AsyncSessionLocal = async_sessionmaker(
#     engine,
#     expire_on_commit=False,
# )


Base = declarative_base()

class CVUser(Base):
    __tablename__ = "cv_user"

    user_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
    username = Column(String, nullable=False, unique=True)
    hashed_password = Column(String, nullable=False)
    email = Column(String, nullable=False, unique=True)
    full_name = Column(String, nullable=False)
    role = Column(String, nullable=False)
    is_active = Column(BOOLEAN, nullable=False)
    tenant_id = Column(UUID(as_uuid=True), primary_key=False)
    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)
    date_modified = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)
    notes = Column(String)

class CVTenant(Base):
    __tablename__ = "cv_tenant"

    tenant_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
    tenant_name = Column(String, nullable=False, unique=True)
    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)
    date_modified = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)
    notes = Column(String)

class CVFile(Base):
    __tablename__ = "cv_file"
    file_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
    user_id = Column(UUID(as_uuid=True), nullable=False)
    file_type = Column(String, nullable=False)
    filename = Column(String, nullable=False)
    url = Column(String, nullable=False)
    is_extracted = Column(BOOLEAN, nullable=False)
    is_deleted = Column(BOOLEAN, nullable=False)
    uploaded_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)
    date_modified = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)

class CVProfile(Base):
    __tablename__ = "cv_profile"

    profile_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
    fullname = Column(String, nullable=False)

    gpa_edu_1 = Column(DOUBLE_PRECISION)
    univ_edu_1 = Column(String)
    major_edu_1 = Column(String)

    gpa_edu_2 = Column(DOUBLE_PRECISION)
    univ_edu_2 = Column(String)
    major_edu_2 = Column(String)

    gpa_edu_3 = Column(DOUBLE_PRECISION)
    univ_edu_3 = Column(String)
    major_edu_3 = Column(String)

    domicile = Column(String)
    yoe = Column(Integer)

    hardskills = Column(ARRAY(String))
    softskills = Column(ARRAY(String))
    certifications = Column(ARRAY(String))
    business_domain = Column(ARRAY(String))

    filename = Column(String, nullable=False)
    file_id = Column(UUID(as_uuid=True), primary_key=False)
    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)


class CVFilter(Base):
    __tablename__ = "cv_filter"

    criteria_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())

    gpa_edu_1 = Column(DOUBLE_PRECISION)
    gpa_edu_2 = Column(DOUBLE_PRECISION)
    gpa_edu_3 = Column(DOUBLE_PRECISION)

    univ_edu_1 = Column(String)
    univ_edu_2 = Column(String)
    univ_edu_3 = Column(String)

    major_edu_1 = Column(String)
    major_edu_2 = Column(String)
    major_edu_3 = Column(String)

    domicile = Column(String)
    yoe = Column(Integer)

    hardskills = Column(ARRAY(String))
    softskills = Column(ARRAY(String))
    certifications = Column(ARRAY(String))
    business_domain = Column(ARRAY(String))

    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)

    
class CVWeight(Base):
    __tablename__ = "cv_weight"

    weight_id = Column(UUID(as_uuid=True), primary_key=True, server_default=master_config.JAKARTA_NOW)
    criteria_id = Column(UUID(as_uuid=True), primary_key=False, nullable=False)

    gpa_edu_1 = Column(DOUBLE_PRECISION)
    gpa_edu_2 = Column(DOUBLE_PRECISION)
    gpa_edu_3 = Column(DOUBLE_PRECISION)

    univ_edu_1 = Column(DOUBLE_PRECISION)
    univ_edu_2 = Column(DOUBLE_PRECISION)
    univ_edu_3 = Column(DOUBLE_PRECISION)

    major_edu_1 = Column(DOUBLE_PRECISION)
    major_edu_2 = Column(DOUBLE_PRECISION)
    major_edu_3 = Column(DOUBLE_PRECISION)

    domicile = Column(DOUBLE_PRECISION)
    yoe = Column(DOUBLE_PRECISION)

    hardskills = Column(DOUBLE_PRECISION)
    softskills = Column(DOUBLE_PRECISION)
    certifications = Column(DOUBLE_PRECISION)
    business_domain = Column(DOUBLE_PRECISION)

    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)


class CVMatching(Base):
    __tablename__ = "cv_matching"

    matching_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
    profile_id = Column(UUID(as_uuid=True))
    weight_id = Column(UUID(as_uuid=True))

    gpa_edu_1 = Column(BOOLEAN)
    gpa_edu_2 = Column(BOOLEAN)
    gpa_edu_3 = Column(BOOLEAN)

    univ_edu_1 = Column(BOOLEAN)
    univ_edu_2 = Column(BOOLEAN)
    univ_edu_3 = Column(BOOLEAN)

    major_edu_1 = Column(BOOLEAN)
    major_edu_2 = Column(BOOLEAN)
    major_edu_3 = Column(BOOLEAN)

    domicile = Column(BOOLEAN)
    yoe = Column(BOOLEAN)

    hardskills = Column(BOOLEAN)
    softskills = Column(BOOLEAN)
    certifications = Column(BOOLEAN)
    business_domain = Column(BOOLEAN)

    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)


class CVScore(Base):
    __tablename__ = "cv_score"

    scoring_id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
    matching_id = Column(UUID(as_uuid=True))
    profile_id = Column(UUID(as_uuid=True))
    score = Column(Integer)

    created_at = Column(TIMESTAMP(timezone=True), server_default=master_config.JAKARTA_NOW)