=== DATABASE SCHEMA ANALYSIS === Total Tables: 21 TABLE: account_transactions ------------------------------ Column Type Null Key id int(11) NO PRI account_id int(11) NO MUL transaction_type enum('credit','debit') NO amount decimal(15,2) NO reference_type varchar(50) NO reference_id int(11) NO description text YES transaction_date datetime YES created_by int(11) YES ================================================== TABLE: activity_logs ------------------------------ Column Type Null Key id int(10) unsigned NO PRI user_id int(10) unsigned YES MUL action varchar(100) NO table_name varchar(100) YES MUL record_id int(10) unsigned YES old_values longtext YES new_values longtext YES description text YES ip_address varchar(45) YES user_agent text YES created_at timestamp NO MUL ================================================== TABLE: capital_transactions ------------------------------ Column Type Null Key id int(10) unsigned NO PRI trans_date date NO investor_id int(10) unsigned NO MUL type enum('injection','withdrawal') NO amount decimal(12,2) NO method enum('cash','bank','mobile') YES reference varchar(100) YES description text YES created_by int(10) unsigned YES MUL created_at timestamp NO ================================================== TABLE: categories ------------------------------ Column Type Null Key id int(10) unsigned NO PRI name varchar(255) NO description text YES is_active tinyint(1) YES created_at timestamp NO ================================================== TABLE: contacts ------------------------------ Column Type Null Key id int(10) unsigned NO PRI type enum('customer','supplier','investor') NO MUL name varchar(255) NO email varchar(255) YES phone varchar(20) YES company_name varchar(255) YES address text YES credit_limit decimal(12,2) YES created_at timestamp NO opening_balance decimal(12,2) YES balance decimal(10,2) YES opening_balance_date date YES ================================================== TABLE: expenses ------------------------------ Column Type Null Key id int(10) unsigned NO PRI category varchar(100) NO amount decimal(12,2) NO reference varchar(100) YES description text YES expense_date date NO MUL created_at timestamp NO ================================================== TABLE: financial_accounts ------------------------------ Column Type Null Key id int(11) NO PRI account_name varchar(100) NO account_number varchar(50) YES account_type enum('cash','bank','mobile_banking') NO balance decimal(15,2) YES is_active tinyint(1) YES created_at timestamp NO updated_at timestamp NO ================================================== TABLE: financial_transactions ------------------------------ Column Type Null Key id int(11) NO PRI financial_account_id int(11) NO MUL transaction_type enum('credit','debit') NO amount decimal(15,2) NO reference_type varchar(50) YES MUL reference_id int(11) YES description text YES transaction_date date NO MUL created_by int(11) YES created_at timestamp NO ================================================== TABLE: payments ------------------------------ Column Type Null Key id int(10) unsigned NO PRI payable_type varchar(50) NO MUL payable_id int(10) unsigned NO amount decimal(12,2) NO method enum('cash','card','bank','mobile') YES paid_at date NO created_at timestamp NO ================================================== TABLE: products ------------------------------ Column Type Null Key id int(10) unsigned NO PRI name varchar(255) NO slug varchar(255) NO UNI sku varchar(100) NO UNI barcode_symbology enum('C128','C39','EAN13','EAN8') YES description text YES category_id int(10) unsigned YES MUL supplier_id int(10) unsigned YES MUL unit_id int(10) unsigned YES MUL cost_price decimal(12,2) YES selling_price decimal(12,2) YES stock_quantity int(11) YES MUL alert_quantity int(11) YES gallery longtext YES is_active tinyint(1) YES created_at timestamp NO updated_at timestamp NO commission_rate decimal(10,2) YES commission_type enum('percentage','fixed') YES ================================================== TABLE: purchase_items ------------------------------ Column Type Null Key id int(10) unsigned NO PRI purchase_id int(10) unsigned NO MUL product_id int(10) unsigned NO MUL quantity int(11) NO unit_cost decimal(12,2) NO subtotal decimal(12,2) NO ================================================== TABLE: purchases ------------------------------ Column Type Null Key id int(10) unsigned NO PRI reference_no varchar(50) NO UNI supplier_id int(10) unsigned YES MUL purchase_date date NO MUL total_amount decimal(12,2) NO paid_amount decimal(12,2) YES agent_commission_percent decimal(5,2) YES agent_commission_amount decimal(10,2) YES status enum('received','pending','canceled') YES MUL created_at timestamp NO ================================================== TABLE: sale_items ------------------------------ Column Type Null Key id int(10) unsigned NO PRI sale_id int(10) unsigned NO MUL product_id int(10) unsigned NO MUL quantity int(11) NO unit_price decimal(12,2) NO subtotal decimal(12,2) NO commission_amount decimal(10,2) YES ================================================== TABLE: sale_return_items ------------------------------ Column Type Null Key id int(10) unsigned NO PRI return_id int(10) unsigned NO MUL product_id int(10) unsigned NO MUL quantity int(11) NO unit_price decimal(12,2) YES subtotal decimal(12,2) YES ================================================== TABLE: sale_returns ------------------------------ Column Type Null Key id int(10) unsigned NO PRI sale_id int(10) unsigned NO MUL return_date date NO total_amount decimal(12,2) YES refund_amount decimal(12,2) YES status enum('pending','completed','rejected') YES MUL reason text YES created_by int(10) unsigned YES MUL created_at timestamp NO ================================================== TABLE: sales ------------------------------ Column Type Null Key id int(10) unsigned NO PRI reference_no varchar(50) NO UNI customer_id int(10) unsigned YES MUL sale_date date NO MUL total_amount decimal(12,2) NO tax_amount decimal(12,2) YES discount_amount decimal(12,2) YES paid_amount decimal(12,2) YES staff_commission_percent decimal(5,2) YES staff_commission_amount decimal(10,2) YES status enum('completed','pending','canceled') YES MUL created_by int(10) unsigned YES MUL created_at timestamp NO ================================================== TABLE: settings ------------------------------ Column Type Null Key id int(10) unsigned NO PRI setting_key varchar(50) NO UNI setting_value text YES setting_type enum('text','number','boolean','json') YES category varchar(50) YES updated_by int(11) YES created_at timestamp NO updated_at timestamp NO ================================================== TABLE: stock_movements ------------------------------ Column Type Null Key id int(10) unsigned NO PRI product_id int(10) unsigned NO MUL type enum('sale','purchase','return','adjustment') NO MUL quantity int(11) NO reference_type varchar(100) YES reference_id int(10) unsigned YES description varchar(255) YES created_at timestamp NO ================================================== TABLE: tax_settings ------------------------------ Column Type Null Key id int(10) unsigned NO PRI tax_name varchar(50) NO tax_rate decimal(5,2) YES is_active tinyint(1) YES is_default tinyint(1) YES created_at timestamp NO ================================================== TABLE: units ------------------------------ Column Type Null Key id int(10) unsigned NO PRI name varchar(100) NO short_name varchar(20) YES ================================================== TABLE: users ------------------------------ Column Type Null Key id int(10) unsigned NO PRI name varchar(255) NO email varchar(255) NO UNI password varchar(255) NO role enum('admin','manager','cashier') YES MUL is_active tinyint(1) YES created_at timestamp NO updated_at timestamp NO ==================================================