import sqlite3
from typing import Optional
import hashlib

class Users:
    def __init__(self, db_path="instance/app_database.db"):
        self.db_path = db_path
        self._create_users_table()

    def _create_users_table(self):
        """Create the users table if it doesn't already exist."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    email TEXT NOT NULL UNIQUE,
                    password TEXT,
                    token TEXT,
                    name TEXT,
                    phone TEXT,
                    address TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    status TEXT NOT NULL DEFAULT 'active',
                    role TEXT DEFAULT 'client'
                );

            """)
            conn.commit()

    def hash_password(self, password: str) -> bytes:
        return hashlib.md5(password.encode('utf-8')).hexdigest()

    def add_user(self, email, passwd, role):
        """Register a new user."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    INSERT INTO users (email, password, role) 
                    VALUES (?, ?, ?)
                """, (email, passwd, role))
                conn.commit()
            return True
        except sqlite3.IntegrityError:
           return False  # Username already exist
        
    def invite_user(self, email, name, phone, address, role='invited'):
        #try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    INSERT INTO users (email, name, phone, address, role) 
                    VALUES (?, ?, ?, ?, ?)
                """, (email, name, phone, address, role))
                conn.commit()
                return cursor.lastrowid
        #except sqlite3.IntegrityError:
        #   return None  

    def register_user(self, email: str, password: str) -> bool:
        """Register a new user."""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    INSERT INTO users (email, password) 
                    VALUES (?, ?)
                """, (email, password))
                conn.commit()
            return True
        except sqlite3.IntegrityError:
            return False  # Username already exist
        
    def authenticate_user(self, email: str, password: str) -> bool:
        """Authenticate a user."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT * FROM users 
                WHERE email = ? AND password = ?
            """, (email, password))
            row = cursor.fetchone()
            if row:
                return {
                    "id": row[0], 
                    "email": row[1], 
                    "token": row[3],
                    "name":row[4],
                    "phone": row[5],
                    "address": row[6],
                    "created_at": row[7],
                    "status": row[8],
                    "role":row[9]
                }
            else:
                return None
        
    def get_user(self, email: str) -> Optional[dict]:
        """Retrieve user details by username."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT * FROM users 
                WHERE email = ?
            """, (email,))
            row = cursor.fetchone()
            if row:
                return {
                    "id": row[0], 
                    "email": row[1], 
                    "token": row[3],
                    "name":row[4],
                    "phone": row[5],
                    "address": row[6],
                    "created_at": row[7],
                    "status": row[8],
                    "role":row[9]
                }
            return None
        
    def get(self, id: int) -> Optional[dict]:
        """Retrieve user details by username."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT * FROM users 
                WHERE id = ?
            """, (id,))
            row = cursor.fetchone()
            if row:
                return {
                    "id": row[0], 
                    "email": row[1], 
                    "token": row[3],
                    "name":row[4],
                    "phone": row[5],
                    "address": row[6],
                    "created_at": row[7],
                    "status": row[8],
                    "role":row[9]
                }
            return None
        
    def get_all(self) -> Optional[dict]:
        """Retrieve user details by username."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT * FROM users 
            """)
            rows = cursor.fetchall()
            if rows:
                buffer = []
                for row in rows:
                    buffer.append({
                        "id": row[0], 
                        "email": row[1], 
                        "token": row[3],
                        "name":row[4],
                        "phone": row[5],
                        "address": row[6],
                        "created_at": row[7],
                        "status": row[8],
                        "role":row[9]
                    })
                return buffer
            return []

    def update_password(self, email, passwd):
        '''Update user password'''
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE users SET password = ?
                WHERE email = ?
            ''', (passwd, email))
            conn.commit()

    def update_token(self, id, token):
        '''Update user token'''
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE users SET token = ?
                WHERE id = ?
            ''', (token, id))
            conn.commit()

    def update_user_data(self, name, phone, address, id):
        '''Update user data'''
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE users SET name = ?, phone = ?, address = ?
                WHERE id = ?
            ''', (name, phone, address, id))
            conn.commit()

    def delete(self, id):
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                DELETE FROM users WHERE id=?;
            ''', (id,))
            conn.commit()