<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateAllTables extends Migration
{
    public function up()
    {
        // Users table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 255, 'unique' => true],
            'password_hash' => ['type' => 'VARCHAR', 'constraint' => 255],
            'first_name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'last_name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'role' => ['type' => 'ENUM', 'constraint' => ['admin','comptable','manager','lecture'], 'default' => 'lecture'],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'last_login' => ['type' => 'DATETIME', 'null' => true],
            'failed_attempts' => ['type' => 'INT', 'constraint' => 3, 'default' => 0],
            'locked_until' => ['type' => 'DATETIME', 'null' => true],
            'remember_token' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('email');
        $this->forge->addKey('role');
        $this->forge->createTable('users');

        // Companies table (multi-tenant ready)
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 255],
            'full_name' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'address' => ['type' => 'TEXT', 'null' => true],
            'city' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'country' => ['type' => 'VARCHAR', 'constraint' => 100, 'default' => 'Tunisie'],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'fax' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'gsm' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'tax_id' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'ice' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'logo' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'currency' => ['type' => 'VARCHAR', 'constraint' => 3, 'default' => 'TND'],
            'default_vat_rate' => ['type' => 'DECIMAL', 'constraint' => '5,4', 'default' => 0.1900],
            'invoice_prefix' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'FAC'],
            'invoice_start_number' => ['type' => 'INT', 'constraint' => 11, 'default' => 1],
            'payment_terms' => ['type' => 'INT', 'constraint' => 3, 'default' => 30],
            'bank_name' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'bank_rib' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'smtp_host' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'smtp_user' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'smtp_pass' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('companies');

        // Clients table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'company_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'code' => ['type' => 'VARCHAR', 'constraint' => 50, 'unique' => true],
            'company_name' => ['type' => 'VARCHAR', 'constraint' => 255],
            'contact_name' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'fax' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'address' => ['type' => 'TEXT', 'null' => true],
            'city' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'country' => ['type' => 'VARCHAR', 'constraint' => 100, 'default' => 'Tunisie'],
            'tax_id' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'ice' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'payment_terms' => ['type' => 'INT', 'constraint' => 3, 'default' => 30],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('company_id');
        $this->forge->addKey('code');
        $this->forge->addForeignKey('company_id', 'companies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('clients');

        // Products/Services table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'company_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'reference' => ['type' => 'VARCHAR', 'constraint' => 100],
            'designation' => ['type' => 'VARCHAR', 'constraint' => 255],
            'category' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'description' => ['type' => 'TEXT', 'null' => true],
            'unit_price' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'vat_rate' => ['type' => 'DECIMAL', 'constraint' => '5,4', 'default' => 0.1900],
            'unit' => ['type' => 'VARCHAR', 'constraint' => 50, 'default' => 'U'],
            'stock_quantity' => ['type' => 'INT', 'constraint' => 11, 'default' => 0],
            'track_stock' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'is_active' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('company_id');
        $this->forge->addKey('reference');
        $this->forge->addForeignKey('company_id', 'companies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('products');

        // Invoices table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'company_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'client_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'invoice_number' => ['type' => 'VARCHAR', 'constraint' => 50, 'unique' => true],
            'invoice_type' => ['type' => 'ENUM', 'constraint' => ['facture','devis','avoir','proforma'], 'default' => 'facture'],
            'status' => ['type' => 'ENUM', 'constraint' => ['brouillon','envoyee','payee','partielle','retard','annulee'], 'default' => 'brouillon'],
            'date' => ['type' => 'DATE'],
            'due_date' => ['type' => 'DATE', 'null' => true],
            'reference_client' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'customs_code' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'reper_number' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'designation_global' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'gross_weight' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'null' => true],
            'weight_unit' => ['type' => 'VARCHAR', 'constraint' => 10, 'default' => 'KGS'],
            'package_count' => ['type' => 'INT', 'constraint' => 11, 'null' => true],
            'currency_value' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'null' => true],
            'currency_unit' => ['type' => 'VARCHAR', 'constraint' => 10, 'default' => 'EUR'],
            'shipper' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'declaration_number' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'regime' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'quittance_number' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'quittance_amount' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'null' => true],
            'subtotal' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'discount_amount' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'discount_percent' => ['type' => 'DECIMAL', 'constraint' => '5,2', 'default' => 0.00],
            'vat_base' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'vat_amount' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'vat_rate' => ['type' => 'DECIMAL', 'constraint' => '5,4', 'default' => 0.1900],
            'stamp_duty' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'total_ht' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'total_ttc' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'advance_amount' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'net_payable' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'amount_in_words' => ['type' => 'TEXT', 'null' => true],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'terms' => ['type' => 'TEXT', 'null' => true],
            'pdf_path' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'sent_at' => ['type' => 'DATETIME', 'null' => true],
            'paid_at' => ['type' => 'DATETIME', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('company_id');
        $this->forge->addKey('client_id');
        $this->forge->addKey('invoice_number');
        $this->forge->addKey('status');
        $this->forge->addKey('date');
        $this->forge->addForeignKey('company_id', 'companies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('client_id', 'clients', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('invoices');

        // Invoice lines table (FRAIS / DEBOURS)
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'invoice_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'line_type' => ['type' => 'ENUM', 'constraint' => ['frais','debours','taxable','autre'], 'default' => 'frais'],
            'sort_order' => ['type' => 'INT', 'constraint' => 3, 'default' => 0],
            'designation' => ['type' => 'VARCHAR', 'constraint' => 255],
            'quantity' => ['type' => 'DECIMAL', 'constraint' => '10,4', 'default' => 1.0000],
            'unit' => ['type' => 'VARCHAR', 'constraint' => 50, 'default' => 'U'],
            'unit_price' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'amount' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'is_taxable' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('invoice_id');
        $this->forge->addForeignKey('invoice_id', 'invoices', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('invoice_lines');

        // Payments table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'invoice_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'amount' => ['type' => 'DECIMAL', 'constraint' => '15,4', 'default' => 0.0000],
            'payment_date' => ['type' => 'DATE'],
            'payment_method' => ['type' => 'ENUM', 'constraint' => ['espece','cheque','virement','carte','autre'], 'default' => 'espece'],
            'reference' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'created_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('invoice_id');
        $this->forge->addForeignKey('invoice_id', 'invoices', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('payments');

        // Audit logs
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'action' => ['type' => 'VARCHAR', 'constraint' => 100],
            'entity_type' => ['type' => 'VARCHAR', 'constraint' => 100],
            'entity_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'old_values' => ['type' => 'JSON', 'null' => true],
            'new_values' => ['type' => 'JSON', 'null' => true],
            'ip_address' => ['type' => 'VARCHAR', 'constraint' => 45, 'null' => true],
            'user_agent' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('user_id');
        $this->forge->addKey('entity_type');
        $this->forge->createTable('audit_logs');
    }

    public function down()
    {
        $this->forge->dropTable('audit_logs');
        $this->forge->dropTable('payments');
        $this->forge->dropTable('invoice_lines');
        $this->forge->dropTable('invoices');
        $this->forge->dropTable('products');
        $this->forge->dropTable('clients');
        $this->forge->dropTable('companies');
        $this->forge->dropTable('users');
    }
}
