from typing import Optional
from uuid import UUID
from app.modules.projects.domain.entities import Project

class ProjectRepository:
    """
    Adapter kết nối Domain Layer với Cơ sở dữ liệu vật lý (PostgreSQL).
    Luôn thao tác trên schema 'projects' để đảm bảo Schema Isolation.
    """
    def __init__(self, conn):
        """Nhận transaction connection từ UnitOfWork (@transactional)"""
        self._conn = conn

    def ensure_schema(self):
        """Đảm bảo bảng tồn tại (Dành cho bản PoC, thực tế sẽ dùng Alembic/Migrations)"""
        with self._conn.cursor() as cur:
            cur.execute("""
                CREATE SCHEMA IF NOT EXISTS projects;
                CREATE TABLE IF NOT EXISTS projects.projects (
                    id UUID PRIMARY KEY,
                    name TEXT NOT NULL,
                    project_code TEXT NOT NULL UNIQUE,
                    start_date TIMESTAMP WITH TIME ZONE,
                    description TEXT,
                    status TEXT,
                    created_at TIMESTAMP WITH TIME ZONE
                );
            """)

    def save(self, project: Project) -> None:
        """Lưu hoặc Cập nhật Entity Project"""
        self.ensure_schema()
        with self._conn.cursor() as cur:
            cur.execute("""
                INSERT INTO projects.projects 
                (id, name, project_code, start_date, description, status, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (id) DO UPDATE SET
                    name = EXCLUDED.name,
                    project_code = EXCLUDED.project_code,
                    start_date = EXCLUDED.start_date,
                    description = EXCLUDED.description,
                    status = EXCLUDED.status;
            """, (
                str(project.id),
                project.name,
                project.project_code,
                project.start_date,
                project.description,
                project.status,
                project.created_at
            ))

    def get_by_id(self, project_id: UUID) -> Optional[Project]:
        with self._conn.cursor() as cur:
            cur.execute("SELECT * FROM projects.projects WHERE id = %s", (str(project_id),))
            row = cur.fetchone()
            if row:
                return Project(**row)
        return None