<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Account_model extends CI_Model {

    protected $table = 'accounts';

    public function __construct() {
        parent::__construct();
    }

    /**
     * Get account by ID
     */
    public function get($id) {
        return $this->db->get_where($this->table, ['id' => $id])->row();
    }

    /**
     * Get account by account number
     */
    public function get_by_account_number($account_number) {
        return $this->db->get_where($this->table, ['account_number' => $account_number])->row();
    }

    /**
     * Get all accounts for a user
     */
    public function get_by_user($user_id) {
        $this->db->select('accounts.*, wallets.ledger_balance, wallets.available_balance, wallets.id as wallet_id');
        $this->db->from($this->table);
        $this->db->join('wallets', 'wallets.account_id = accounts.id AND wallets.currency = accounts.currency', 'left');
        $this->db->where('accounts.user_id', $user_id);
        $this->db->order_by('accounts.is_primary', 'DESC');
        $this->db->order_by('accounts.created_at', 'ASC');
        return $this->db->get()->result();
    }

    /**
     * Get primary account for a user
     */
    public function get_primary($user_id) {
        $this->db->select('accounts.*, wallets.ledger_balance, wallets.available_balance, wallets.id as wallet_id');
        $this->db->from($this->table);
        $this->db->join('wallets', 'wallets.account_id = accounts.id AND wallets.currency = accounts.currency', 'left');
        $this->db->where('accounts.user_id', $user_id);
        $this->db->where('accounts.is_primary', 1);
        return $this->db->get()->row();
    }

    /**
     * Get all accounts with optional filters
     */
    public function get_all($filters = [], $limit = null, $offset = 0) {
        $this->db->select('accounts.*, users.first_name, users.last_name, users.email, wallets.ledger_balance, wallets.available_balance');
        $this->db->from($this->table);
        $this->db->join('users', 'users.id = accounts.user_id', 'left');
        $this->db->join('wallets', 'wallets.account_id = accounts.id AND wallets.currency = accounts.currency', 'left');
        
        if (!empty($filters['status'])) {
            $this->db->where('accounts.status', $filters['status']);
        }
        if (!empty($filters['account_type'])) {
            $this->db->where('accounts.account_type', $filters['account_type']);
        }
        if (!empty($filters['search'])) {
            $this->db->group_start();
            $this->db->like('accounts.account_number', $filters['search']);
            $this->db->or_like('accounts.account_name', $filters['search']);
            $this->db->or_like('users.first_name', $filters['search']);
            $this->db->or_like('users.last_name', $filters['search']);
            $this->db->group_end();
        }
        
        $this->db->order_by('accounts.created_at', 'DESC');
        
        if ($limit) {
            $this->db->limit($limit, $offset);
        }
        
        return $this->db->get()->result();
    }

    /**
     * Create new account
     */
    public function create($data) {
        $data['account_number'] = $this->generate_account_number();
        $data['created_at'] = date('Y-m-d H:i:s');
        
        $this->db->insert($this->table, $data);
        $account_id = $this->db->insert_id();
        
        // Create default wallet for the account
        if ($account_id) {
            $this->load->model('Wallet_model');
            $this->Wallet_model->create([
                'account_id' => $account_id,
                'currency' => $data['currency'] ?? 'USD'
            ]);
        }
        
        return $account_id;
    }

    /**
     * Update account
     */
    public function update($id, $data) {
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->db->update($this->table, $data, ['id' => $id]);
    }

    /**
     * Delete account
     */
    public function delete($id) {
        return $this->db->delete($this->table, ['id' => $id]);
    }

    /**
     * Generate unique account number
     */
    public function generate_account_number() {
        do {
            // Generate 10-digit account number starting with 2
            $account_number = '2' . str_pad(mt_rand(0, 999999999), 9, '0', STR_PAD_LEFT);
        } while ($this->account_number_exists($account_number));
        
        return $account_number;
    }

    /**
     * Check if account number exists
     */
    public function account_number_exists($account_number) {
        return $this->db->where('account_number', $account_number)
                       ->count_all_results($this->table) > 0;
    }

    /**
     * Freeze account
     */
    public function freeze($id, $reason, $admin_id) {
        return $this->db->update($this->table, [
            'status' => 'frozen',
            'frozen_reason' => $reason,
            'frozen_at' => date('Y-m-d H:i:s'),
            'frozen_by' => $admin_id,
            'updated_at' => date('Y-m-d H:i:s')
        ], ['id' => $id]);
    }

    /**
     * Unfreeze account
     */
    public function unfreeze($id) {
        return $this->db->update($this->table, [
            'status' => 'active',
            'frozen_reason' => null,
            'frozen_at' => null,
            'frozen_by' => null,
            'updated_at' => date('Y-m-d H:i:s')
        ], ['id' => $id]);
    }

    /**
     * Set account as primary
     */
    public function set_primary($user_id, $account_id) {
        // First, unset all primary flags for user
        $this->db->update($this->table, ['is_primary' => 0], ['user_id' => $user_id]);
        // Then set the specified account as primary
        return $this->db->update($this->table, ['is_primary' => 1], ['id' => $account_id]);
    }

    /**
     * Get account with user details
     */
    public function get_with_user($id) {
        $this->db->select('accounts.*, users.first_name, users.last_name, users.email, users.phone, users.profile_photo');
        $this->db->from($this->table);
        $this->db->join('users', 'users.id = accounts.user_id');
        $this->db->where('accounts.id', $id);
        return $this->db->get()->row();
    }

    /**
     * Get total balance for all accounts of a user
     */
    public function get_total_balance($user_id, $currency = 'USD') {
        $this->db->select_sum('wallets.available_balance', 'total');
        $this->db->from($this->table);
        $this->db->join('wallets', 'wallets.account_id = accounts.id');
        $this->db->where('accounts.user_id', $user_id);
        $this->db->where('accounts.status', 'active');
        $this->db->where('wallets.currency', $currency);
        $result = $this->db->get()->row();
        return $result ? floatval($result->total) : 0;
    }

    /**
     * Update last activity
     */
    public function update_last_activity($id) {
        return $this->db->update($this->table, [
            'last_activity_at' => date('Y-m-d H:i:s')
        ], ['id' => $id]);
    }

    /**
     * Get dormant accounts
     */
    public function get_dormant_accounts($days = 365) {
        $date_threshold = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        $this->db->where('last_activity_at <', $date_threshold);
        $this->db->where('status', 'active');
        return $this->db->get($this->table)->result();
    }

    /**
     * Count accounts
     */
    public function count_all($filters = []) {
        if (!empty($filters['status'])) {
            $this->db->where('status', $filters['status']);
        }
        if (!empty($filters['account_type'])) {
            $this->db->where('account_type', $filters['account_type']);
        }
        return $this->db->count_all_results($this->table);
    }

    /**
     * Get account tier
     */
    public function get_tier($tier_id) {
        return $this->db->get_where('account_tiers', ['id' => $tier_id])->row();
    }

    /**
     * Get all account tiers
     */
    public function get_all_tiers() {
        return $this->db->get('account_tiers')->result();
    }
}
