<?php
// admin/accounts/install-tables.php
// Run this file once to create all accounting tables

require_once __DIR__ . '/../../includes/config.php';
require_once __DIR__ . '/../../includes/functions.php';

if (!isAdmin()) {
    header('Location: ../login.php');
    exit;
}

// SQL for creating all accounting tables
$sql = "
-- Bank Accounts Table
CREATE TABLE IF NOT EXISTS `bank_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_name` varchar(255) NOT NULL,
  `account_number` varchar(50) NOT NULL,
  `bank_name` varchar(255) NOT NULL,
  `branch_name` varchar(255) DEFAULT NULL,
  `account_type` enum('savings','current','loan','fixed_deposit') DEFAULT 'savings',
  `currency` varchar(10) DEFAULT 'BDT',
  `opening_balance` decimal(10,2) DEFAULT 0.00,
  `current_balance` decimal(10,2) DEFAULT 0.00,
  `status` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_number` (`account_number`)
);

-- Deposits Table
CREATE TABLE IF NOT EXISTS `deposits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deposit_no` varchar(50) NOT NULL,
  `account_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `deposit_date` date NOT NULL,
  `payment_method` enum('cash','bank','bkash','nagad','rocket','card','other') DEFAULT 'cash',
  `reference_no` varchar(100) DEFAULT NULL,
  `source` varchar(100) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `deposit_no` (`deposit_no`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `deposits_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL
);

-- Withdrawals Table
CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `withdrawal_no` varchar(50) NOT NULL,
  `account_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `withdrawal_date` date NOT NULL,
  `payment_method` enum('cash','bank','bkash','nagad','rocket','card','other') DEFAULT 'cash',
  `reference_no` varchar(100) DEFAULT NULL,
  `purpose` varchar(255) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `withdrawal_no` (`withdrawal_no`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `withdrawals_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL
);

-- Transactions Summary Table (for quick balance calculation)
CREATE TABLE IF NOT EXISTS `account_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `transaction_type` enum('deposit','withdrawal','transfer_in','transfer_out') NOT NULL,
  `reference_no` varchar(50) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `transaction_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `account_transactions_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE CASCADE
);

-- Insert sample bank accounts
INSERT INTO `bank_accounts` (`account_name`, `account_number`, `bank_name`, `branch_name`, `account_type`, `opening_balance`, `current_balance`) VALUES
('SellBazar Main Account', 'SB-001-123456', 'Dutch Bangla Bank', 'Motijheel Branch', 'current', 100000, 100000),
('SellBazar Savings', 'SB-001-789012', 'BRAC Bank', 'Gulshan Branch', 'savings', 50000, 50000);
";

// Execute SQL
$message = '';
$error = '';

try {
    // Split SQL into individual statements
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    $success_count = 0;
    $error_count = 0;
    
    foreach ($statements as $statement) {
        if (!empty($statement)) {
            try {
                $pdo->exec($statement);
                $success_count++;
            } catch (PDOException $e) {
                // Check if table already exists (ignore duplicate table errors)
                if (strpos($e->getMessage(), 'already exists') === false) {
                    $error_count++;
                    $error .= "Error: " . $e->getMessage() . "<br>";
                } else {
                    $success_count++;
                }
            }
        }
    }
    
    if ($error_count == 0) {
        $message = "All accounting tables created successfully! ($success_count tables)";
    } else {
        $message = "Tables created with $error_count errors. $success_count tables created successfully.";
    }
} catch (Exception $e) {
    $error = "Error: " . $e->getMessage();
}

include '../header.php';
?>

<style>
.install-container {
    max-width: 800px;
    margin: 50px auto;
    padding: 30px;
    background: #fff;
    border-radius: 12px;
    box-shadow: 0 5px 30px rgba(0,0,0,0.1);
}

.install-container h2 {
    color: #1e293b;
    margin-bottom: 20px;
}

.install-container .success {
    color: #22c55e;
    padding: 15px;
    background: #f0fdf4;
    border-radius: 8px;
    border-left: 4px solid #22c55e;
}

.install-container .error {
    color: #ef4444;
    padding: 15px;
    background: #fef2f2;
    border-radius: 8px;
    border-left: 4px solid #ef4444;
}

.table-list {
    margin: 20px 0;
    padding: 0;
    list-style: none;
}

.table-list li {
    padding: 8px 0;
    border-bottom: 1px solid #f1f5f9;
    display: flex;
    align-items: center;
    gap: 10px;
}

.table-list li .badge {
    padding: 2px 10px;
    border-radius: 20px;
    font-size: 12px;
}

.badge-success {
    background: #22c55e;
    color: #fff;
}

.badge-danger {
    background: #ef4444;
    color: #fff;
}
</style>

<div class="admin-content">
    <div class="install-container">
        <h2><i class="fa-solid fa-database"></i> Install Accounting Tables</h2>
        
        <?php if ($message): ?>
        <div class="success">
            <i class="fa-solid fa-check-circle"></i> <?php echo $message; ?>
        </div>
        <?php endif; ?>
        
        <?php if ($error): ?>
        <div class="error">
            <i class="fa-solid fa-exclamation-circle"></i> <?php echo $error; ?>
        </div>
        <?php endif; ?>
        
        <h4 style="margin-top: 25px;">Tables to be created:</h4>
        <ul class="table-list">
            <li>
                <span class="badge badge-success">✓</span>
                <strong>vendors</strong> - Supplier/Vendor management
            </li>
            <li>
                <span class="badge badge-success">✓</span>
                <strong>purchase_orders</strong> - Purchase Orders
            </li>
            <li>
                <span class="badge badge-success">✓</span>
                <strong>po_items</strong> - Purchase Order Items
            </li>
            <li>
                <span class="badge badge-success">✓</span>
                <strong>invoices</strong> - Customer Invoices
            </li>
            <li>
                <span class="badge badge-success">✓</span>
                <strong>income</strong> - Customer Payments Received
            </li>
            <li>
                <span class="badge badge-success">✓</span>
                <strong>expenses</strong> - Business Expenses
            </li>
            <li>
                <span class="badge badge-success">✓</span>
                <strong>po_payments</strong> - PO Payments to Vendors
            </li>
        </ul>
        
        <div style="margin-top: 25px; padding: 15px; background: #f8fafc; border-radius: 8px;">
            <p><strong>Note:</strong> If tables already exist, they will be preserved. No data will be lost.</p>
            <a href="dashboard.php" class="btn btn-primary mt-2">
                <i class="fa-solid fa-arrow-right"></i> Go to Accounts Dashboard
            </a>
            <a href="install-tables.php" class="btn btn-secondary mt-2 ms-2">
                <i class="fa-solid fa-rotate"></i> Re-run Installation
            </a>
        </div>
    </div>
</div>

<?php include '../footer.php'; ?>