import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';

import '../models/work_order.dart';

class AppDatabase {
  AppDatabase._();

  static final AppDatabase instance = AppDatabase._();

  Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;

    final databasePath = await getDatabasesPath();
    _database = await openDatabase(
      p.join(databasePath, 'ot_movil.db'),
      version: 6,
      onConfigure: (database) async {
        await database.execute('PRAGMA foreign_keys = ON');
      },
      onCreate: (database, version) async {
        await database.execute('''
          CREATE TABLE work_orders (
            id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            status TEXT NOT NULL,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            completed_at TEXT,
            client TEXT NOT NULL DEFAULT '',
            phone TEXT NOT NULL DEFAULT '',
            address TEXT NOT NULL DEFAULT '',
            labor_cost INTEGER NOT NULL DEFAULT 0,
            recipient_email TEXT,
            pdf_path TEXT
          )
        ''');
        await database.execute('''
          CREATE TABLE work_tasks (
            id TEXT PRIMARY KEY,
            work_order_id TEXT NOT NULL,
            title TEXT NOT NULL,
            observation TEXT NOT NULL,
            position INTEGER NOT NULL,
            FOREIGN KEY (work_order_id)
              REFERENCES work_orders (id) ON DELETE CASCADE
          )
        ''');
        await database.execute('''
          CREATE TABLE task_photos (
            id TEXT PRIMARY KEY,
            task_id TEXT NOT NULL,
            kind TEXT NOT NULL,
            path TEXT NOT NULL,
            FOREIGN KEY (task_id)
              REFERENCES work_tasks (id) ON DELETE CASCADE
          )
        ''');
        await database.execute('''
          CREATE TABLE task_parts (
            id TEXT PRIMARY KEY,
            task_id TEXT NOT NULL,
            name TEXT NOT NULL,
            price INTEGER NOT NULL,
            position INTEGER NOT NULL,
            FOREIGN KEY (task_id)
              REFERENCES work_tasks (id) ON DELETE CASCADE
          )
        ''');
        await database.execute(
          'CREATE INDEX idx_tasks_order ON work_tasks(work_order_id)',
        );
        await database.execute(
          'CREATE INDEX idx_photos_task ON task_photos(task_id)',
        );
        await database.execute(
          'CREATE INDEX idx_parts_task ON task_parts(task_id)',
        );
      },
      onUpgrade: (database, oldVersion, newVersion) async {
        if (oldVersion < 2) {
          await database.execute(
            "ALTER TABLE work_orders "
            "ADD COLUMN client TEXT NOT NULL DEFAULT ''",
          );
          await database.execute(
            "ALTER TABLE work_orders "
            "ADD COLUMN address TEXT NOT NULL DEFAULT ''",
          );
        }
        if (oldVersion < 3) {
          await database.execute('''
            CREATE TABLE task_parts (
              id TEXT PRIMARY KEY,
              task_id TEXT NOT NULL,
              name TEXT NOT NULL,
              price INTEGER NOT NULL,
              position INTEGER NOT NULL,
              FOREIGN KEY (task_id)
                REFERENCES work_tasks (id) ON DELETE CASCADE
            )
          ''');
          await database.execute(
            'CREATE INDEX idx_parts_task ON task_parts(task_id)',
          );
        }
        if (oldVersion < 4) {
          await database.execute(
            'ALTER TABLE work_orders '
            'ADD COLUMN labor_cost INTEGER NOT NULL DEFAULT 0',
          );
        }
        if (oldVersion < 5) {
          await database.execute(
            "ALTER TABLE work_orders "
            "ADD COLUMN phone TEXT NOT NULL DEFAULT ''",
          );
        }
      },
    );
    return _database!;
  }

  Future<List<WorkOrder>> getWorkOrders() async {
    final db = await database;
    final rows = await db.query('work_orders', orderBy: 'updated_at DESC');

    final orders = <WorkOrder>[];
    for (final row in rows) {
      orders.add(await _workOrderFromRow(db, row));
    }
    return orders;
  }

  Future<WorkOrder?> getWorkOrder(String id) async {
    final db = await database;
    final rows = await db.query(
      'work_orders',
      where: 'id = ?',
      whereArgs: [id],
      limit: 1,
    );
    if (rows.isEmpty) return null;
    return _workOrderFromRow(db, rows.first);
  }

  Future<WorkOrder> _workOrderFromRow(
    DatabaseExecutor db,
    Map<String, Object?> row,
  ) async {
    final orderId = row['id']! as String;
    final taskRows = await db.query(
      'work_tasks',
      where: 'work_order_id = ?',
      whereArgs: [orderId],
      orderBy: 'position ASC',
    );

    final tasks = <WorkTask>[];
    for (final taskRow in taskRows) {
      final taskId = taskRow['id']! as String;
      final photoRows = await db.query(
        'task_photos',
        where: 'task_id = ?',
        whereArgs: [taskId],
        orderBy: 'rowid ASC',
      );
      final partRows = await db.query(
        'task_parts',
        where: 'task_id = ?',
        whereArgs: [taskId],
        orderBy: 'position ASC',
      );
      tasks.add(
        WorkTask(
          id: taskId,
          title: taskRow['title']! as String,
          observation: taskRow['observation']! as String,
          position: taskRow['position']! as int,
          photos: photoRows
              .map(
                (photoRow) => TaskPhoto(
                  id: photoRow['id']! as String,
                  path: photoRow['path']! as String,
                  kind: PhotoKind.fromDatabase(photoRow['kind']! as String),
                ),
              )
              .toList(),
          parts: partRows
              .map(
                (partRow) => TaskPart(
                  id: partRow['id']! as String,
                  name: partRow['name']! as String,
                  price: partRow['price']! as int,
                ),
              )
              .toList(),
        ),
      );
    }

    return WorkOrder(
      id: orderId,
      title: row['title']! as String,
      status: WorkOrderStatus.fromDatabase(row['status']! as String),
      createdAt: DateTime.parse(row['created_at']! as String),
      updatedAt: DateTime.parse(row['updated_at']! as String),
      completedAt: row['completed_at'] == null
          ? null
          : DateTime.parse(row['completed_at']! as String),
      details: WorkOrderDetails(
        client: row['client']! as String,
        phone: row['phone']! as String,
        address: row['address']! as String,
      ),
      laborCost: row['labor_cost']! as int,
      recipientEmail: row['recipient_email'] as String?,
      pdfPath: row['pdf_path'] as String?,
      tasks: tasks,
    );
  }

  Future<void> saveWorkOrder(WorkOrder order) async {
    final db = await database;
    await db.transaction((transaction) async {
      await transaction.insert('work_orders', {
        'id': order.id,
        'title': order.title,
        'status': order.status.databaseValue,
        'created_at': order.createdAt.toIso8601String(),
        'updated_at': order.updatedAt.toIso8601String(),
        'completed_at': order.completedAt?.toIso8601String(),
        'client': order.details.client,
        'phone': order.details.phone,
        'address': order.details.address,
        'labor_cost': order.laborCost,
        'recipient_email': order.recipientEmail,
        'pdf_path': order.pdfPath,
      }, conflictAlgorithm: ConflictAlgorithm.replace);

      await transaction.delete(
        'work_tasks',
        where: 'work_order_id = ?',
        whereArgs: [order.id],
      );

      for (final task in order.tasks) {
        await transaction.insert('work_tasks', {
          'id': task.id,
          'work_order_id': order.id,
          'title': task.title,
          'observation': task.observation,
          'position': task.position,
        });
        for (final photo in task.photos) {
          await transaction.insert('task_photos', {
            'id': photo.id,
            'task_id': task.id,
            'kind': photo.kind.databaseValue,
            'path': photo.path,
          });
        }
        for (var index = 0; index < task.parts.length; index++) {
          final part = task.parts[index];
          await transaction.insert('task_parts', {
            'id': part.id,
            'task_id': task.id,
            'name': part.name,
            'price': part.price,
            'position': index,
          });
        }
      }
    });
  }

  Future<void> deleteWorkOrder(String id) async {
    final db = await database;
    await db.delete('work_orders', where: 'id = ?', whereArgs: [id]);
  }
}