import { toast } from '@backpackapp-io/react-native-toast';
import * as SQLite from 'expo-sqlite';
import { IEntity } from '../../models/IEntity';
import { IQuery } from '../../models/IQuery';
import { IDataService } from './IDataService';


export class SQLiteDataService<T extends IEntity> implements IDataService<T> {
    private static db: SQLite.SQLiteDatabase | null = null;
    private tableName: string;

    constructor(queryObj: IQuery) {
        this.tableName = queryObj.tableName;
        SQLiteDataService.openDatabase(queryObj.tableQuery);
    }

    static openDatabase(tableQuery: string): SQLite.SQLiteDatabase | null {
        try {
            if (SQLiteDataService.db == null) {
                SQLiteDataService.db = SQLite.openDatabaseSync('mydb2');
            }
            const response = SQLiteDataService.db.runSync(tableQuery);

        }
        catch (error) {
            console.error('Error opening database or running table query:', error);
            toast.error(error);
        }
        console.log('Database opened and table created if not exists');
        return SQLiteDataService.db;
    }

    async create(item: T): Promise<T> {
        const db = await this.getDb();
        const id = await this.add(item);
        return { ...item, id } as T;

    }
    async findById(id: number): Promise<T | null> {
        const db = await this.getDb();
        return await db.getFirstAsync<T>(`SELECT * FROM ${this.tableName} WHERE id = ?`, [id]) || null;
    }
    async findAll(): Promise<T[]> {
        const db = await this.getDb();
        return await db.getAllAsync<T>(`SELECT * FROM ${this.tableName}`);
    }

    private async getDb(): Promise<SQLite.SQLiteDatabase> {
        if (!SQLiteDataService.db) {
            SQLiteDataService.db = await SQLite.openDatabaseAsync('habitask');
        }
        return SQLiteDataService.db;
    }

    async add(item: T): Promise<number> {
        const db = await this.getDb();
        const keys = Object.keys(item).filter(k => k !== 'id');
        const values = keys.map(k => (item as any)[k]);
        const placeholders = keys.map(() => '?').join(', ');
        const result = await db.runAsync(
            `INSERT INTO ${this.tableName} (${keys.join(', ')}) VALUES (${placeholders})`,
            ...values);
        return result.lastInsertRowId;
    }

    async update(id: number, item: Partial<T>): Promise<T | null> {
        const db = await this.getDb();
        if (!item.id) {
            throw new Error('Item must have an id to update');
        }
        const keys = Object.keys(item).filter(k => k !== 'id');
        const values = keys.map(k => (item as any)[k]);
        const setClause = keys.map(k => `${k} = ?`).join(', ');
        try {
            await db.runAsync(
                `UPDATE ${this.tableName} SET ${setClause} WHERE id = ?`,
                ...values, item.id);
            return await this.findById(id);
        } catch (error) {
            console.error(`Error updating item with id ${item.id}:`, error);
            return null;
        }
    }

    async delete(id: number): Promise<boolean> {
        const db = await this.getDb();
        try {
            await db.runAsync(`DELETE FROM ${this.tableName} WHERE id = ?`, [id]);
            return true;
        } catch (error) {
            console.error(`Error deleting item with id ${id}:`, error);
            return false;
        }
    }
}