import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'dart:convert';
import '../models/customer.dart';
import '../models/product.dart';
import '../models/estimate.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('customer_assist.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);
    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
    );
  }

  Future<void> _createDB(Database db, int version) async {
    await db.execute('CREATE TABLE customers (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_code TEXT NOT NULL, name TEXT NOT NULL, phone_number TEXT, email TEXT NOT NULL, address TEXT, sales_person_id INTEGER, tax_rate INTEGER DEFAULT 8, discount_rate INTEGER DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE employees (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, position TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE warehouses (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE suppliers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, address TEXT, phone_number TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, product_code TEXT NOT NULL, name TEXT NOT NULL, unit_price INTEGER NOT NULL, quantity INTEGER DEFAULT 0, stock INTEGER DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE sales (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER NOT NULL, sale_date TEXT NOT NULL, total_amount INTEGER NOT NULL, tax_rate INTEGER DEFAULT 8, product_items TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE estimates (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_code TEXT NOT NULL, estimate_number TEXT NOT NULL, product_items TEXT, total_amount INTEGER NOT NULL, tax_rate INTEGER DEFAULT 8, status TEXT DEFAULT "open", expiry_date TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE inventory (id INTEGER PRIMARY KEY AUTOINCREMENT, product_code TEXT UNIQUE NOT NULL, name TEXT NOT NULL, unit_price INTEGER NOT NULL, stock INTEGER DEFAULT 0, min_stock INTEGER DEFAULT 0, max_stock INTEGER DEFAULT 1000, supplier_name TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    await db.execute('CREATE TABLE invoices (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_code TEXT NOT NULL, invoice_number TEXT NOT NULL, sale_date TEXT NOT NULL, total_amount INTEGER NOT NULL, tax_rate INTEGER DEFAULT 8, status TEXT DEFAULT "paid", product_items TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL)');
    print('Database created with version: 1');
  }

  // Customer API
  Future<int> insertCustomer(Customer customer) async {
    final db = await database;
    return await db.insert('customers', customer.toMap());
  }

  Future<Customer?> getCustomer(int id) async {
    final db = await database;
    final results = await db.query('customers', where: 'id = ?', whereArgs: [id]);
    if (results.isEmpty) return null;
    return Customer.fromMap(results.first);
  }

  Future<List<Customer>> getCustomers() async {
    final db = await database;
    final results = await db.query('customers');
    return results.map((e) => Customer.fromMap(e)).toList();
  }

  Future<int> updateCustomer(Customer customer) async {
    final db = await database;
    return await db.update('customers', customer.toMap(), where: 'id = ?', whereArgs: [customer.id]);
  }

  Future<int> deleteCustomer(int id) async {
    final db = await database;
    return await db.delete('customers', where: 'id = ?', whereArgs: [id]);
  }

  // Product API
  Future<int> insertProduct(Product product) async {
    final db = await database;
    return await db.insert('products', product.toMap());
  }

  Future<Product?> getProduct(int id) async {
    final db = await database;
    final results = await db.query('products', where: 'id = ?', whereArgs: [id]);
    if (results.isEmpty) return null;
    return Product.fromMap(results.first);
  }

  Future<List<Product>> getProducts() async {
    final db = await database;
    final results = await db.query('products');
    return results.map((e) => Product.fromMap(e)).toList();
  }

  Future<int> updateProduct(Product product) async {
    final db = await database;
    return await db.update('products', product.toMap(), where: 'id = ?', whereArgs: [product.id]);
  }

  Future<int> deleteProduct(int id) async {
    final db = await database;
    return await db.delete('products', where: 'id = ?', whereArgs: [id]);
  }

  // Sales API
  Future<int> insertSales(Map<String, dynamic> salesData) async {
    final db = await database;
    return await db.insert('sales', salesData);
  }

  Future<List<Map<String, dynamic>>> getSales() async {
    final db = await database;
    return await db.query('sales');
  }

  Future<int> updateSales(Map<String, dynamic> salesData) async {
    final db = await database;
    return await db.update('sales', salesData, where: 'id = ?', whereArgs: [salesData['id'] as int]);
  }

  Future<int> deleteSales(int id) async {
    final db = await database;
    return await db.delete('sales', where: 'id = ?', whereArgs: [id]);
  }

  // Estimate API（単純化）
  Future<int> insertEstimate(Map<String, dynamic> estimateData) async {
    final db = await database;
    return await db.insert('estimates', estimateData);
  }

  Future<List<Map<String, dynamic>>> getEstimates() async {
    final db = await database;
    return await db.query('estimates');
  }

  Future<int> updateEstimate(Map<String, dynamic> estimateData) async {
    final db = await database;
    return await db.update('estimates', estimateData, where: 'id = ?', whereArgs: [estimateData['id'] as int]);
  }

  Future<int> deleteEstimate(int id) async {
    final db = await database;
    return await db.delete('estimates', where: 'id = ?', whereArgs: [id]);
  }

  // Invoice API
  Future<int> insertInvoice(Map<String, dynamic> invoiceData) async {
    final db = await database;
    return await db.insert('invoices', invoiceData);
  }

  Future<List<Map<String, dynamic>>> getInvoices() async {
    final db = await database;
    return await db.query('invoices');
  }

  Future<int> updateInvoice(Map<String, dynamic> invoiceData) async {
    final db = await database;
    return await db.update('invoices', invoiceData, where: 'id = ?', whereArgs: [invoiceData['id'] as int]);
  }

  Future<int> deleteInvoice(int id) async {
    final db = await database;
    return await db.delete('invoices', where: 'id = ?', whereArgs: [id]);
  }

  // Inventory API
  Future<int> insertInventory(Map<String, dynamic> inventoryData) async {
    final db = await database;
    return await db.insert('inventory', inventoryData);
  }

  Future<List<Map<String, dynamic>>> getInventory() async {
    final db = await database;
    return await db.query('inventory');
  }

  Future<int> updateInventory(Map<String, dynamic> inventoryData) async {
    final db = await database;
    return await db.update('inventory', inventoryData, where: 'id = ?', whereArgs: [inventoryData['id'] as int]);
  }

  Future<int> deleteInventory(int id) async {
    final db = await database;
    return await db.delete('inventory', where: 'id = ?', whereArgs: [id]);
  }

  // Employee API
  Future<int> insertEmployee(Map<String, dynamic> employeeData) async {
    final db = await database;
    return await db.insert('employees', employeeData);
  }

  Future<List<Map<String, dynamic>>> getEmployees() async {
    final db = await database;
    return await db.query('employees');
  }

  Future<int> updateEmployee(Map<String, dynamic> employeeData) async {
    final db = await database;
    return await db.update('employees', employeeData, where: 'id = ?', whereArgs: [employeeData['id'] as int]);
  }

  Future<int> deleteEmployee(int id) async {
    final db = await database;
    return await db.delete('employees', where: 'id = ?', whereArgs: [id]);
  }

  // Warehouse API
  Future<int> insertWarehouse(Map<String, dynamic> warehouseData) async {
    final db = await database;
    return await db.insert('warehouses', warehouseData);
  }

  Future<List<Map<String, dynamic>>> getWarehouses() async {
    final db = await database;
    return await db.query('warehouses');
  }

  Future<int> updateWarehouse(Map<String, dynamic> warehouseData) async {
    final db = await database;
    return await db.update('warehouses', warehouseData, where: 'id = ?', whereArgs: [warehouseData['id'] as int]);
  }

  Future<int> deleteWarehouse(int id) async {
    final db = await database;
    return await db.delete('warehouses', where: 'id = ?', whereArgs: [id]);
  }

  // Supplier API
  Future<int> insertSupplier(Map<String, dynamic> supplierData) async {
    final db = await database;
    return await db.insert('suppliers', supplierData);
  }

  Future<List<Map<String, dynamic>>> getSuppliers() async {
    final db = await database;
    return await db.query('suppliers');
  }

  Future<int> updateSupplier(Map<String, dynamic> supplierData) async {
    final db = await database;
    return await db.update('suppliers', supplierData, where: 'id = ?', whereArgs: [supplierData['id'] as int]);
  }

  Future<int> deleteSupplier(int id) async {
    final db = await database;
    return await db.delete('suppliers', where: 'id = ?', whereArgs: [id]);
  }

  Future<void> close() async {
    final db = await database;
    db.close();
  }
}