MAINHACK

Viewing / Editing File: database.sql

-- ====================================
-- حسابات عربية بن فريش - قاعدة البيانات
-- Arabic Credit Sales Management System
-- ====================================

CREATE DATABASE IF NOT EXISTS hesabat_arabia CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hesabat_arabia;

-- ====================================
-- جدول المستخدمين (Users Table)
-- ====================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('manager', 'accountant', 'representative') NOT NULL,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول العملاء (Customers Table)
-- ====================================
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    address TEXT,
    current_debt DECIMAL(10,2) DEFAULT 0.00,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_phone (phone),
    INDEX idx_current_debt (current_debt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول الورديات (Shifts Table)
-- ====================================
CREATE TABLE shifts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    representative_id INT NOT NULL,
    start_stock INT NOT NULL,
    start_cash DECIMAL(10,2) NOT NULL,
    end_stock INT DEFAULT NULL,
    end_cash DECIMAL(10,2) DEFAULT NULL,
    start_approved_by INT DEFAULT NULL,
    end_approved_by INT DEFAULT NULL,
    status ENUM('pending_start', 'active', 'pending_end', 'closed') DEFAULT 'pending_start',
    start_time TIMESTAMP NULL DEFAULT NULL,
    end_time TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (start_approved_by) REFERENCES users(id),
    FOREIGN KEY (end_approved_by) REFERENCES users(id),
    INDEX idx_representative (representative_id),
    INDEX idx_status (status),
    INDEX idx_start_time (start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول الفواتير (Invoices Table)
-- ====================================
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    representative_id INT NOT NULL,
    shift_id INT NOT NULL,
    previous_debt DECIMAL(10,2) NOT NULL,
    invoice_total DECIMAL(10,2) NOT NULL,
    new_total_debt DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0.00,
    remaining_amount DECIMAL(10,2) NOT NULL,
    status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
    notes TEXT,
    whatsapp_sent TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id),
    INDEX idx_invoice_number (invoice_number),
    INDEX idx_customer (customer_id),
    INDEX idx_shift (shift_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول بنود الفاتورة (Invoice Items Table)
-- ====================================
CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    INDEX idx_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول المدفوعات (Payments Table)
-- ====================================
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    representative_id INT NOT NULL,
    shift_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    previous_debt DECIMAL(10,2) NOT NULL,
    new_debt DECIMAL(10,2) NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'other') DEFAULT 'cash',
    notes TEXT,
    whatsapp_sent TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id),
    INDEX idx_customer (customer_id),
    INDEX idx_shift (shift_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول تطبيق المدفوعات على الفواتير
-- (Payment Applications to Invoices)
-- ====================================
CREATE TABLE payment_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id INT NOT NULL,
    invoice_id INT NOT NULL,
    amount_applied DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    INDEX idx_payment (payment_id),
    INDEX idx_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول المرتجعات (Returns Table)
-- ====================================
CREATE TABLE returns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    representative_id INT NOT NULL,
    shift_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_value DECIMAL(10,2) NOT NULL,
    reason TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approved_by INT DEFAULT NULL,
    approved_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    INDEX idx_invoice (invoice_id),
    INDEX idx_shift (shift_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول سجل العمليات (Activity Log)
-- ====================================
CREATE TABLE activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    action_description TEXT NOT NULL,
    reference_id INT DEFAULT NULL,
    reference_type VARCHAR(50) DEFAULT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id),
    INDEX idx_action_type (action_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- جدول الإعدادات (Settings Table)
-- ====================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_by INT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- البيانات الافتراضية (Default Data)
-- ====================================

-- إضافة مدير النظام الافتراضي (password: admin123)
INSERT INTO users (name, phone, password, role) VALUES
('المدير العام', '+201000000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'manager');

-- إعدادات WhatsApp الافتراضية
INSERT INTO settings (setting_key, setting_value, updated_by) VALUES
('whatsapp_api_url', '', 1),
('whatsapp_api_token', '', 1),
('company_name', 'حسابات عربية بن فريش', 1),
('company_phone', '+20', 1);

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: database.sql

Server Info

System Features

User Info