Database Schema Report
Table: assets_cctv
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | NO | | | |
| camera_no | varchar(20) | YES | | | |
| description | text | YES | | | |
| floor_area | varchar(100) | YES | | | |
| location_details | text | YES | | | |
| megapixel | varchar(20) | YES | | | |
| model | varchar(100) | YES | | | |
| dvr_connection | varchar(50) | YES | | | |
| channel_no | varchar(20) | YES | | | |
| client_id | int(11) | NO | | | |
Table: assets_network
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | NO | | | |
| ip_address | varchar(20) | YES | | | |
| device_type | varchar(100) | YES | | | |
| username | varchar(50) | YES | | | |
| password | varchar(100) | YES | | | |
| location | varchar(100) | YES | | | |
Table: assets_pcs
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | NO | | | |
| pc_id | varchar(50) | YES | | | |
| employee_name | varchar(100) | YES | | | |
| job_title | varchar(100) | YES | | | |
| os_type | varchar(50) | YES | | | |
| computer_name | varchar(100) | YES | | | |
| processor | varchar(100) | YES | | | |
| ram | varchar(50) | YES | | | |
| hard_drive | varchar(100) | YES | | | |
| rustdesk_id | varchar(50) | YES | | | |
| password | varchar(50) | YES | | | |
| floor | varchar(50) | YES | | | |
| client_id | int(11) | NO | | | |
Table: client_files
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | YES | | | |
| file_name | varchar(255) | YES | | | |
Table: contract_payments
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | YES | | | |
| payment_date | date | YES | | | |
| amount | decimal(10,2) | YES | | | |
| notes | text | YES | | | |
| created_at | timestamp | NO | | current_timestamp() | |
| status | varchar(20) | YES | | pending | |
Table: contracts
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_status | varchar(50) | YES | | | |
| client_name | varchar(200) | YES | | | |
| contact_person | varchar(200) | YES | | | |
| job_title | varchar(200) | YES | | | |
| phone | varchar(50) | YES | | | |
| address | text | YES | | | |
| payment_plan | varchar(50) | YES | | | |
| visits | int(11) | YES | | | |
| created_at | timestamp | NO | | current_timestamp() | |
| contract_value | decimal(10,2) | NO | | 0.00 | |
| currency | varchar(10) | YES | | | |
| location_link | text | YES | | | |
| start_date | date | YES | | | |
| end_date | date | YES | | | |
| contract_duration | int(11) | YES | | | |
| contract_file | text | YES | | | |
| client_id | int(11) | NO | | | |
| username | varchar(50) | YES | UNI | | |
| password | varchar(255) | YES | | | |
Table: customers
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| name | varchar(255) | NO | | | |
| phone1 | varchar(50) | YES | | | |
| phone1_name | varchar(100) | YES | | | |
| phone2 | varchar(50) | YES | | | |
| phone2_name | varchar(100) | YES | | | |
| email1 | varchar(255) | YES | | | |
| email2 | varchar(255) | YES | | | |
| created_at | timestamp | NO | | current_timestamp() | |
Table: engineer_sessions
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| engineer_id | int(11) | YES | | | |
| login_time | datetime | YES | | | |
| logout_time | datetime | YES | | | |
| status | varchar(20) | YES | | active | |
Table: engineer_tracking
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| session_id | int(11) | YES | | | |
| engineer_id | int(11) | YES | | | |
| latitude | varchar(50) | YES | | | |
| longitude | varchar(50) | YES | | | |
| track_time | datetime | YES | | current_timestamp() | |
Table: engineers
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| name | varchar(150) | YES | | | |
| phone | varchar(50) | YES | | | |
| email | varchar(150) | YES | | | |
| specialization | varchar(150) | YES | | | |
| contract_file | varchar(255) | YES | | | |
Table: invoice_installments
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| invoice_id | int(11) | YES | | | |
| amount | decimal(10,2) | YES | | | |
| due_date | date | YES | | | |
| status | varchar(50) | YES | | unpaid | |
Table: invoices
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | NO | | | |
| invoice_date | datetime | YES | | current_timestamp() | |
| total_amount | decimal(10,2) | YES | | 0.00 | |
| sub_total | decimal(10,2) | YES | | 0.00 | |
| tax_percent | decimal(5,2) | YES | | 0.00 | |
| tax_amount | decimal(10,2) | YES | | 0.00 | |
| notes | text | YES | | | |
Table: pages
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| page_key | varchar(100) | NO | UNI | | |
| page_name | varchar(150) | NO | | | |
Table: permissions
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| user_id | int(11) | NO | MUL | | |
| page_id | int(11) | NO | MUL | | |
Table: products
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| p_name | varchar(255) | NO | | | |
| category | varchar(100) | YES | | | |
| description | text | YES | | | |
| brand | varchar(100) | YES | | | |
| supplier_id | int(11) | YES | | | |
| quantity | int(11) | YES | | 0 | |
| buying_price | decimal(10,2) | YES | | 0.00 | |
| selling_price | decimal(10,2) | YES | | 0.00 | |
| min_quantity | int(11) | YES | | 5 | |
| created_at | timestamp | NO | | current_timestamp() | |
| barcode | varchar(50) | YES | UNI | | |
Table: purchases
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| product_id | int(11) | YES | | | |
| supplier_id | int(11) | YES | | | |
| p_name | varchar(255) | YES | | | |
| quantity | int(11) | YES | | | |
| purchase_price | decimal(10,2) | YES | | | |
| purchase_date | timestamp | NO | | current_timestamp() | |
Table: quotation_items
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| quotation_id | int(11) | NO | MUL | | |
| product_name | varchar(255) | NO | | | |
| quantity | int(11) | NO | | | |
| selling_price | decimal(10,2) | NO | | | |
Table: quotations
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| customer_name | varchar(255) | NO | | | |
| tax_percentage | decimal(5,2) | YES | | 0.00 | |
| special_terms | text | YES | | | |
| grand_total | decimal(10,2) | NO | | 0.00 | |
| created_at | timestamp | NO | | current_timestamp() | |
Table: received_payments
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | YES | | | |
| payment_date | date | YES | | | |
| amount | decimal(10,2) | YES | | | |
| payment_method | varchar(50) | YES | | | |
| notes | text | YES | | | |
Table: sales
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| customer_name | varchar(255) | YES | | | |
| product_id | int(11) | YES | | | |
| p_name | varchar(255) | YES | | | |
| quantity | int(11) | YES | | | |
| buying_price | decimal(10,2) | YES | | | |
| selling_price | decimal(10,2) | YES | | | |
| sale_date | timestamp | NO | | current_timestamp() | |
| invoice_id | int(11) | YES | | | |
| quantity_delivered | int(11) | YES | | 0 | |
| quantity_pending | int(11) | YES | | 0 | |
| delivery_due_date | date | YES | | | |
| item_barcode | varchar(100) | YES | | | |
Table: suppliers
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| name | varchar(255) | NO | | | |
| phone | varchar(50) | YES | | | |
| address | text | YES | | | |
| created_at | timestamp | NO | | current_timestamp() | |
Table: users
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| username | varchar(50) | YES | | | |
| password | varchar(255) | YES | | | |
Table: visits
| Field |
Type |
Null |
Key |
Default |
Extra |
| id | int(11) | NO | PRI | | auto_increment |
| contract_id | int(11) | YES | | | |
| visit_date | date | YES | | | |
| engineer_name | varchar(150) | YES | | | |
| report_file | varchar(255) | YES | | | |
| notes | text | YES | | | |
| engineer_id | int(11) | YES | | | |
| client_id | int(11) | NO | | | |