PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE branches (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                code TEXT NOT NULL,
                address TEXT,
                city TEXT DEFAULT 'Erode',
                state TEXT DEFAULT 'Tamil Nadu',
                pincode TEXT DEFAULT '638001',
                phone TEXT,
                email TEXT,
                gstin TEXT,
                is_active INTEGER DEFAULT 1,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO branches VALUES(1,'Erode Main Branch & Showroom','ERD-01','42/1, Brough Road, Near Clock Tower','Erode','Tamil Nadu','638001','+91 98427 12345','main@erodeautomobiles.com','33AAAAE1234F1Z5',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO branches VALUES(2,'Perundurai Bypass Heavy Workshop','PND-02','SF 108/2, NH-544 Salem-Cbe Highway, Perundurai Bypass','Perundurai','Tamil Nadu','638052','+91 98427 54321','perundurai@erodeautomobiles.com','33AAAAE1234F1Z5',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO branches VALUES(3,'Bhavani Road Express Service Hub','BHV-03','77, Bhavani Main Road, BP Agraharam','Bhavani','Tamil Nadu','638005','+91 98427 99887','bhavani@erodeautomobiles.com','33AAAAE1234F1Z5',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE roles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                slug TEXT NOT NULL UNIQUE,
                description TEXT,
                is_system INTEGER DEFAULT 0,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO roles VALUES(1,'Super Admin','super_admin','Full system command center & consolidated multi-branch controls',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(2,'Service Manager','service_manager','Estimates, workshop workflow, technician assignments, delivery approval',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(3,'Floor Manager','floor_manager','Vehicle check-in, initial inspections, complaints checklist, floor routing',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(4,'Receptionist','receptionist','Customer desk, appointments, CRM check-in, phone/walkin bookings',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(5,'Sales Manager','sales_manager','Vehicle inventory, leads pipeline, quotations, vehicle sales billing',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(6,'Store / Stock Manager','store_manager','Spare parts inventory, part issues to job cards, purchase orders, reorders',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(7,'Technician','technician','Mobile workbench, job execution timer, part requests, inspection notes',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(8,'Accountant','accountant','Cash book, bank, expenses, GST returns, supplier payables, P&L reports',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(9,'Billing Staff','billing_staff','Counter billing, GST invoices, receipts, payment QR handling',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO roles VALUES(10,'Customer','customer','Customer portal, live service tracking, estimate approval, service history',1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE permissions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                slug TEXT NOT NULL UNIQUE,
                category TEXT NOT NULL,
                created_at TEXT
            );
INSERT INTO permissions VALUES(1,'View Dashboards','view_dashboard','Dashboard','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(2,'View All Branches Consolidated','view_all_branches','Branch','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(3,'Manage Vehicle Sales & Quotes','manage_sales','Sales','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(4,'Manage Leads & CRM','manage_leads','Sales','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(5,'Manage Workshop & Job Cards','manage_garage','Garage','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(6,'Create & Manage Inspections','manage_inspections','Garage','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(7,'Approve Service Estimates','approve_estimates','Garage','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(8,'Assign Technicians to Jobs','assign_technicians','Garage','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(9,'Manage Spare Parts & Stock','manage_inventory','Inventory','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(10,'Approve & Issue Parts to Job Cards','issue_parts','Inventory','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(11,'Manage Suppliers & Purchase Bills','manage_purchases','Purchases','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(12,'Create GST & Service Bills','create_invoices','Billing','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(13,'Record Payments & Receipts','collect_payments','Billing','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(14,'Manage Daybook, Cash, Bank & Ledgers','manage_accounts','Accounts','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(15,'View Sensitive P&L & Balance Sheet','view_financial_reports','Accounts','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(16,'Manage Staff, Roles & Permissions','manage_users','Settings','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(17,'Manage System Settings & Backups','manage_settings','Settings','2026-10-01 11:12:51');
INSERT INTO permissions VALUES(18,'Access AI Business Assistant & Analytics','ai_assistant','AI','2026-10-01 11:12:51');
CREATE TABLE role_permissions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                role_id INTEGER NOT NULL,
                permission_id INTEGER NOT NULL
            );
INSERT INTO role_permissions VALUES(1,1,18);
INSERT INTO role_permissions VALUES(2,1,7);
INSERT INTO role_permissions VALUES(3,1,8);
INSERT INTO role_permissions VALUES(4,1,13);
INSERT INTO role_permissions VALUES(5,1,12);
INSERT INTO role_permissions VALUES(6,1,10);
INSERT INTO role_permissions VALUES(7,1,14);
INSERT INTO role_permissions VALUES(8,1,5);
INSERT INTO role_permissions VALUES(9,1,6);
INSERT INTO role_permissions VALUES(10,1,9);
INSERT INTO role_permissions VALUES(11,1,4);
INSERT INTO role_permissions VALUES(12,1,11);
INSERT INTO role_permissions VALUES(13,1,3);
INSERT INTO role_permissions VALUES(14,1,17);
INSERT INTO role_permissions VALUES(15,1,16);
INSERT INTO role_permissions VALUES(16,1,2);
INSERT INTO role_permissions VALUES(17,1,1);
INSERT INTO role_permissions VALUES(18,1,15);
CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                role_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                mobile TEXT NOT NULL UNIQUE,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                api_token TEXT,
                avatar TEXT,
                specialization TEXT,
                hourly_rate REAL DEFAULT 0.00,
                status TEXT DEFAULT 'active',
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO users VALUES(1,1,1,'K. Selvakumar (Super Admin)','admin@erodeautomobiles.com','9842711001','admin','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_ec378a450874bdb900ccb5526b86b39a',NULL,'Super Admin & Owner',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(2,1,2,'R. Prakash (Service Manager)','service@erodeautomobiles.com','9842711002','service_mgr','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_1a86be3e8d4a2557338349b9739864a6',NULL,'Diagnostic & Mechanical Expert',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(3,1,3,'M. Vignesh (Floor Manager)','floor@erodeautomobiles.com','9842711003','floor_mgr','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_576ed5ad7e851653d574f9ee4e5ed854',NULL,'Vehicle Reception & Inspection Lead',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(4,1,4,'S. Priya (Receptionist)','reception@erodeautomobiles.com','9842711004','reception','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_c721e9ae8d91df8541a50a54f945e688',NULL,'Front Desk & Customer Care',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(5,1,5,'K. Rajesh (Sales Manager)','sales@erodeautomobiles.com','9842711005','sales_mgr','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_7fa5a4b536c866e80be3884557c6f0d9',NULL,'New & Pre-Owned Car Sales Specialist',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(6,1,6,'A. Senthil (Store Manager)','store@erodeautomobiles.com','9842711006','store_mgr','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_d5c24f68c529f6017a403e17796e408f',NULL,'OEM Spare Inventory Controller',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(7,1,7,'P. Hariharan (Master Tech)','hari@erodeautomobiles.com','9842711007','tech_hari','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_7562f6b9a38d4fbad913561c9e2e1f59',NULL,'Engine Overhaul & Diagnostics',350.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(8,1,7,'G. Karthik (Senior Tech)','karthik@erodeautomobiles.com','9842711008','tech_karthik','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_b8eb460c6ef7dc5f9aedbf6c5abba659',NULL,'Electrical, ECU & AC Specialist',300.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(9,1,7,'S. Murugan (Suspension Tech)','murugan@erodeautomobiles.com','9842711009','tech_murugan','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_e1876f050407e54fcb867e1ac560c9c1',NULL,'Brakes, Suspension & Wheel Alignment',250.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(10,1,8,'V. Natesan (Chief Accountant)','accounts@erodeautomobiles.com','9842711010','accountant','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_90575a2db8eb136d3754100197b73d18',NULL,'GST & Tally Double Entry Certified',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(11,1,9,'T. Kavitha (Billing Lead)','billing@erodeautomobiles.com','9842711011','billing','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_4aa32901b1e2e6c6deafccd9de4b9fdb',NULL,'Counter & Service Invoicing',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO users VALUES(12,1,10,'Dr. K. Sridhar (Customer)','sridhar@erodehealth.com','9842722001','cust_sridhar','$2y$12$0t3frIcbJfjOZh84iKMB6OYOA/zsISy83wNduQ4PcIGV.2LgO2t6W','erd_token_f3efeb10e186f4bb8dfe92e91a48b1f2',NULL,'Loyal VIP Customer',0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE login_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                ip_address TEXT,
                user_agent TEXT,
                status TEXT,
                branch_id INTEGER,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO login_history VALUES(1,1,'127.0.0.1','Unknown','success',1,'2026-10-01 06:04:39');
INSERT INTO login_history VALUES(2,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-01 06:05:15');
INSERT INTO login_history VALUES(3,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-01 06:06:55');
INSERT INTO login_history VALUES(4,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','failed',1,'2026-10-01 06:12:40');
INSERT INTO login_history VALUES(5,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','failed',1,'2026-10-01 06:12:42');
INSERT INTO login_history VALUES(6,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-01 06:12:44');
INSERT INTO login_history VALUES(7,1,'192.168.1.10','curl/8.7.1','success',1,'2026-10-01 06:17:36');
INSERT INTO login_history VALUES(8,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-01 06:18:27');
INSERT INTO login_history VALUES(9,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-01 06:31:06');
INSERT INTO login_history VALUES(10,1,'192.168.1.28','Mozilla/5.0 (X11; CrOS x86_64 14541.0.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/152.0.0.0 Safari/537.36','success',1,'2026-10-01 06:35:00');
INSERT INTO login_history VALUES(11,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-01 06:35:44');
INSERT INTO login_history VALUES(12,1,'192.168.1.10','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-01 06:36:24');
INSERT INTO login_history VALUES(13,1,'127.0.0.1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-08 02:08:49');
INSERT INTO login_history VALUES(14,1,'127.0.0.1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-08 02:10:44');
INSERT INTO login_history VALUES(15,1,'127.0.0.1','Mozilla/5.0 (X11; CrOS x86_64 14541.0.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/154.0.0.0 Safari/537.36','success',1,'2026-10-08 02:11:29');
INSERT INTO login_history VALUES(16,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:13:00');
INSERT INTO login_history VALUES(17,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:14:35');
INSERT INTO login_history VALUES(18,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:15:18');
INSERT INTO login_history VALUES(19,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:16:27');
INSERT INTO login_history VALUES(20,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:38:46');
INSERT INTO login_history VALUES(21,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:39:00');
INSERT INTO login_history VALUES(22,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 02:39:49');
INSERT INTO login_history VALUES(23,1,'127.0.0.1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-08 02:56:18');
INSERT INTO login_history VALUES(24,1,'127.0.0.1','WhatsApp/2.23.20.0','success',1,'2026-10-08 02:56:34');
INSERT INTO login_history VALUES(25,1,'127.0.0.1','Mozilla/5.0 (X11; CrOS x86_64 14541.0.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/154.0.0.0 Safari/537.36','success',1,'2026-10-08 02:58:40');
INSERT INTO login_history VALUES(26,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 03:04:00');
INSERT INTO login_history VALUES(27,5,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 03:04:00');
INSERT INTO login_history VALUES(28,1,'127.0.0.1','curl/8.7.1','success',1,'2026-10-08 03:04:00');
INSERT INTO login_history VALUES(29,1,'127.0.0.1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/155.0.0.0 Safari/537.36','success',1,'2026-10-08 03:14:41');
INSERT INTO login_history VALUES(30,1,'127.0.0.1','Mozilla/5.0 (X11; CrOS x86_64 14541.0.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/154.0.0.0 Safari/537.36','success',1,'2026-10-08 03:15:07');
CREATE TABLE audit_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                branch_id INTEGER,
                module TEXT NOT NULL,
                action TEXT NOT NULL,
                record_id INTEGER,
                old_value TEXT,
                new_value TEXT,
                ip_address TEXT,
                created_at TEXT
            );
CREATE TABLE customers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                customer_code TEXT UNIQUE,
                name TEXT NOT NULL,
                mobile TEXT NOT NULL,
                whatsapp TEXT,
                email TEXT,
                address TEXT,
                city TEXT DEFAULT 'Erode',
                gstin TEXT,
                pan TEXT,
                source TEXT DEFAULT 'Walk-in',
                loyalty_points INTEGER DEFAULT 0,
                outstanding_balance REAL DEFAULT 0.00,
                status TEXT DEFAULT 'active',
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO customers VALUES(1,1,'CUST-ERD-0001','Dr. K. Sridhar MBBS','9842722001','9842722001','sridhar@erodehealth.com','12, Gandhiraj Road, Periyar Nagar','Erode','33AAAPL9876C1Z1','AAAPL9876C','Doctor Referral',550,0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customers VALUES(2,1,'CUST-ERD-0002','Thiru. M. Shanmugam','9842722002','9842722002','shanmugam.textiles@gmail.com','104, Cauvery Road, BP Agraharam','Erode','33AABCS4455R1Z8','AABCS4455R','Existing Client',320,2450.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customers VALUES(3,1,'CUST-ERD-0003','R. Anitha B.Tech','9842722003','9842722003','anitha.erode@yahoo.com','55/3, Sampath Nagar Main Road','Erode','','','Website Booking',150,0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customers VALUES(4,1,'CUST-ERD-0004','K. Balasubramaniam (Advocate)','9842722004','9842722004','bala.advocate@gmail.com','Court Complex Road, Kollampalayam','Erode','','','Walk-in',420,0.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customers VALUES(5,1,'CUST-ERD-0005','G. Dhanapal (Transports)','9842722005','9842722005','dhanapal.lorry@gmail.com','88, Perundurai Road, Near Solar Bus Stand','Erode','33AABBD1122K1Z3','AABBD1122K','Corporate Fleet',890,8500.0,'active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customers VALUES(6,1,'CUST-WEB-4373','mandy','8220718028','8220718028',NULL,NULL,'Erode',NULL,NULL,'Walk-in',0,0.0,'active','2026-10-08 02:11:04','2026-10-08 02:11:04');
CREATE TABLE customer_vehicles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                customer_id INTEGER NOT NULL,
                reg_number TEXT NOT NULL,
                brand TEXT NOT NULL,
                model TEXT NOT NULL,
                variant TEXT,
                fuel_type TEXT DEFAULT 'Petrol',
                transmission TEXT DEFAULT 'Manual',
                model_year INTEGER,
                color TEXT,
                engine_number TEXT,
                chassis_number TEXT,
                current_km INTEGER DEFAULT 0,
                last_service_date TEXT,
                next_service_due TEXT,
                insurance_expiry TEXT,
                puc_expiry TEXT,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO customer_vehicles VALUES(1,1,'TN-33-AB-1234','Hyundai','Creta SX(O)','1.5 CRDi Diesel','Diesel','Manual',2023,'Polar White','ENG-CR-45120','CHS-CR-99881',32450,'2026-03-10','2026-09-10','2027-02-15','2027-02-15','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customer_vehicles VALUES(2,2,'TN-33-BV-5678','Tata Motors','Nexon XZ+ (O)','1.2L Revotron','Petrol','Manual',2022,'Foliage Green','ENG-NX-77123','CHS-NX-33441',48200,'2026-01-20','2026-07-20','2026-11-30','2026-11-30','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customer_vehicles VALUES(3,3,'TN-33-CA-9988','Mahindra','XUV700 AX7 Luxury','2.2L mHawk AWD','Diesel','Automatic',2024,'Midnight Blue','ENG-XU-11244','CHS-XU-55667',18600,'2026-05-14','2026-11-14','2027-04-20','2027-04-20','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customer_vehicles VALUES(4,4,'TN-33-DZ-4321','Honda','City ZX 5th Gen','1.5L i-VTEC','Petrol','CVT',2021,'Golden Brown Metallic','ENG-CT-88991','CHS-CT-22119',61200,'2025-11-18','2026-05-18','2026-12-10','2026-12-10','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customer_vehicles VALUES(5,5,'TN-33-EF-7766','Toyota','Innova Crysta 2.4 VX','2.4L GD Diesel','Diesel','Manual',2020,'Silver Metallic','ENG-IN-33221','CHS-IN-88776',114500,'2026-04-02','2026-10-02','2027-01-05','2027-01-05','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO customer_vehicles VALUES(6,6,'TN 45 8545','Honda','activa',NULL,'Petrol','Manual',NULL,NULL,NULL,NULL,30000,NULL,NULL,NULL,NULL,'2026-10-08 02:11:04','2026-10-08 02:11:04');
INSERT INTO customer_vehicles VALUES(7,6,'TN 45 7584','Tata','2015',NULL,'Petrol','Manual',NULL,NULL,NULL,NULL,2000,'2026-10-08 02:35:07',NULL,NULL,NULL,'2026-10-08 02:35:07','2026-10-08 02:35:07');
CREATE TABLE vehicle_brands (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                logo TEXT,
                country TEXT DEFAULT 'India',
                is_active INTEGER DEFAULT 1
            );
INSERT INTO vehicle_brands VALUES(1,'Tata Motors',NULL,'India',1);
INSERT INTO vehicle_brands VALUES(2,'Hyundai',NULL,'South Korea',1);
INSERT INTO vehicle_brands VALUES(3,'Mahindra',NULL,'India',1);
INSERT INTO vehicle_brands VALUES(4,'Maruti Suzuki',NULL,'Japan / India',1);
INSERT INTO vehicle_brands VALUES(5,'Toyota',NULL,'Japan',1);
INSERT INTO vehicle_brands VALUES(6,'Honda',NULL,'Japan',1);
INSERT INTO vehicle_brands VALUES(7,'Kia',NULL,'South Korea',1);
INSERT INTO vehicle_brands VALUES(8,'Volkswagen',NULL,'Germany',1);
INSERT INTO vehicle_brands VALUES(9,'Škoda',NULL,'Czech Republic',1);
INSERT INTO vehicle_brands VALUES(10,'MG Motor',NULL,'UK / Global',1);
CREATE TABLE vehicle_models (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                brand_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                category TEXT DEFAULT 'SUV',
                fuel_types TEXT DEFAULT 'Petrol,Diesel',
                is_active INTEGER DEFAULT 1
            );
INSERT INTO vehicle_models VALUES(1,1,'Nexon EV / Petrol','Compact SUV','Petrol,Electric,Diesel',1);
INSERT INTO vehicle_models VALUES(2,1,'Harrier','Mid SUV','Diesel',1);
INSERT INTO vehicle_models VALUES(3,1,'Safari','7-Seater Premium SUV','Diesel',1);
INSERT INTO vehicle_models VALUES(4,1,'Punch','Micro SUV','Petrol,CNG,Electric',1);
INSERT INTO vehicle_models VALUES(5,2,'Creta 2026 Facelift','Mid SUV','Petrol,Diesel',1);
INSERT INTO vehicle_models VALUES(6,2,'Venue','Compact SUV','Petrol,Diesel',1);
INSERT INTO vehicle_models VALUES(7,2,'Tucson','Luxury SUV','Petrol,Diesel',1);
INSERT INTO vehicle_models VALUES(8,3,'XUV700 AX7L','Luxury 7-Seater','Petrol,Diesel',1);
INSERT INTO vehicle_models VALUES(9,3,'Scorpio-N','Rugged SUV','Petrol,Diesel',1);
INSERT INTO vehicle_models VALUES(10,3,'Thar Roxx 4x4','Offroad SUV','Petrol,Diesel',1);
INSERT INTO vehicle_models VALUES(11,4,'Swift ZXi+','Hatchback','Petrol,CNG',1);
INSERT INTO vehicle_models VALUES(12,4,'Grand Vitara Hybrid','Hybrid SUV','Hybrid,Petrol',1);
INSERT INTO vehicle_models VALUES(13,5,'Innova Hycross ZX(O)','MPV Hybrid','Hybrid,Petrol',1);
INSERT INTO vehicle_models VALUES(14,5,'Fortuner Legender','Heavy SUV','Diesel,Petrol',1);
INSERT INTO vehicle_models VALUES(15,6,'City ZX e:HEV','Sedan Hybrid','Hybrid,Petrol',1);
CREATE TABLE vehicle_inventory (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                vin TEXT UNIQUE,
                brand TEXT NOT NULL,
                model TEXT NOT NULL,
                variant TEXT NOT NULL,
                color TEXT NOT NULL,
                fuel_type TEXT NOT NULL,
                transmission TEXT NOT NULL,
                model_year INTEGER NOT NULL,
                engine_number TEXT,
                chassis_number TEXT,
                purchase_date TEXT,
                supplier_id INTEGER,
                purchase_price REAL NOT NULL,
                extra_expenses REAL DEFAULT 0.00,
                landing_cost REAL NOT NULL,
                ex_showroom_price REAL NOT NULL,
                min_selling_price REAL NOT NULL,
                selling_price REAL NOT NULL,
                gst_rate REAL DEFAULT 28.00,
                status TEXT DEFAULT 'AVAILABLE', -- AVAILABLE, BOOKED, SOLD, TRANSFERRED
                stock_age_days INTEGER DEFAULT 0,
                images TEXT,
                features TEXT,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO vehicle_inventory VALUES(1,1,'VIN-2026-HYU-CR01','Hyundai','Creta 2026','SX(O) Turbo DCT','Titan Grey Matte','Petrol','Automatic',2026,'ENG-HYU-88741','CHS-HYU-99231','2026-08-15',NULL,1420000.0,15000.0,1435000.0,1850000.0,1820000.0,1925000.0,28.0,'AVAILABLE',46,NULL,NULL,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO vehicle_inventory VALUES(2,1,'VIN-2026-TAT-NX02','Tata Motors','Nexon Fearless+ S','1.2 Turbo Revotron','Daytona Grey Dual Tone','Petrol','Manual',2026,'ENG-TAT-33214','CHS-TAT-44122','2026-09-01',NULL,980000.0,10000.0,990000.0,1320000.0,1290000.0,1375000.0,28.0,'AVAILABLE',30,NULL,NULL,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO vehicle_inventory VALUES(3,1,'VIN-2026-MAH-XU03','Mahindra','XUV700 AX7L AWD','2.2L mHawk Turbo','Midnight Black','Diesel','Automatic',2026,'ENG-MAH-77451','CHS-MAH-88210','2026-07-10',NULL,2050000.0,25000.0,2075000.0,2690000.0,2650000.0,2780000.0,28.0,'BOOKED',82,NULL,NULL,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO vehicle_inventory VALUES(4,2,'VIN-2026-TOY-IN04','Toyota','Innova Hycross','ZX(O) Strong Hybrid','Blackish Ageha','Hybrid','e-CVT',2026,'ENG-TOY-11223','CHS-TOY-44556','2026-08-20',NULL,2550000.0,30000.0,2580000.0,3150000.0,3100000.0,3280000.0,28.0,'AVAILABLE',41,NULL,NULL,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO vehicle_inventory VALUES(5,1,'VIN-2026-MAR-SW05','Maruti Suzuki','Swift ZXi+ Dual Tone','1.2L Z-Series Dual VVT','Luster Blue / Midnight Black','Petrol','Manual',2026,'ENG-MAR-66321','CHS-MAR-77112','2026-09-12',NULL,680000.0,8000.0,688000.0,895000.0,880000.0,935000.0,28.0,'AVAILABLE',19,NULL,NULL,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO vehicle_inventory VALUES(6,3,'VIN-2026-HON-CT06','Honda','City ZX e:HEV','1.5L Atkinson Cycle','Radiant Red Metallic','Hybrid','e-CVT',2026,'ENG-HON-99412','CHS-HON-55210','2026-06-25',NULL,1580000.0,18000.0,1598000.0,1990000.0,1950000.0,2065000.0,28.0,'AVAILABLE',97,NULL,NULL,'2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE leads (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                lead_code TEXT UNIQUE,
                customer_id INTEGER,
                name TEXT NOT NULL,
                mobile TEXT NOT NULL,
                email TEXT,
                interested_in TEXT DEFAULT 'Vehicle Purchase', -- 'Vehicle Purchase', 'Service', 'Spare Parts'
                brand_preference TEXT,
                model_preference TEXT,
                budget_range TEXT,
                source TEXT DEFAULT 'Walk-in',
                stage TEXT DEFAULT 'NEW', -- NEW, CONTACTED, QUOTATION_SENT, NEGOTIATION, BOOKED, WON, LOST
                assigned_to INTEGER,
                notes TEXT,
                next_followup_date TEXT,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO leads VALUES(1,1,'LEAD-2026-001',1,'Dr. K. Sridhar MBBS','9842722001','sridhar@erodehealth.com','Vehicle Purchase','Hyundai','Creta 2026 SX(O)','₹ 18 - 20 Lakhs','Walk-in','NEGOTIATION',5,'Customer looking for immediate delivery on Titan Grey color','2026-10-03','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO leads VALUES(2,1,'LEAD-2026-002',NULL,'C. Senthilkumar','9842733002','senthil.builder@gmail.com','Vehicle Purchase','Mahindra','XUV700 AX7L','₹ 25 - 28 Lakhs','Website','QUOTATION_SENT',5,'Quotation sent via WhatsApp. Test drive requested.','2026-10-02','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO leads VALUES(3,1,'LEAD-2026-003',3,'R. Anitha B.Tech','9842722003','anitha.erode@yahoo.com','Service','Mahindra','XUV700','','Phone Call','WON',3,'Scheduled for 20,000 KM Periodic Service','2026-10-01','2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE lead_followups (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                lead_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                followup_type TEXT DEFAULT 'Phone Call',
                notes TEXT NOT NULL,
                next_action TEXT,
                followup_date TEXT,
                created_at TEXT
            );
CREATE TABLE quotations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                quotation_number TEXT UNIQUE NOT NULL,
                lead_id INTEGER,
                customer_id INTEGER NOT NULL,
                vehicle_id INTEGER,
                vehicle_details TEXT,
                ex_showroom_price REAL NOT NULL,
                gst_amount REAL DEFAULT 0.00,
                tcs_amount REAL DEFAULT 0.00,
                registration_charges REAL DEFAULT 0.00,
                insurance_charges REAL DEFAULT 0.00,
                accessories_charges REAL DEFAULT 0.00,
                fastag_charges REAL DEFAULT 500.00,
                extended_warranty REAL DEFAULT 0.00,
                discount_amount REAL DEFAULT 0.00,
                final_on_road_price REAL NOT NULL,
                valid_until TEXT,
                status TEXT DEFAULT 'ACTIVE', -- ACTIVE, ACCEPTED, REJECTED, EXPIRED
                created_by INTEGER,
                created_at TEXT,
                updated_at TEXT
            );
CREATE TABLE vehicle_sales (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                sale_number TEXT UNIQUE NOT NULL,
                customer_id INTEGER NOT NULL,
                vehicle_id INTEGER NOT NULL,
                quotation_id INTEGER,
                sales_advisor_id INTEGER NOT NULL,
                sale_date TEXT NOT NULL,
                total_amount REAL NOT NULL,
                discount_amount REAL DEFAULT 0.00,
                tax_amount REAL DEFAULT 0.00,
                paid_amount REAL DEFAULT 0.00,
                outstanding_amount REAL DEFAULT 0.00,
                delivery_date TEXT,
                status TEXT DEFAULT 'BOOKED', -- BOOKED, INVOICED, DELIVERED, CANCELLED
                payment_status TEXT DEFAULT 'PARTIAL', -- UNPAID, PARTIAL, FULLY_PAID
                created_at TEXT,
                updated_at TEXT
            );
CREATE TABLE service_bookings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                booking_number TEXT UNIQUE NOT NULL,
                customer_id INTEGER,
                customer_name TEXT NOT NULL,
                mobile TEXT NOT NULL,
                whatsapp TEXT,
                vehicle_number TEXT NOT NULL,
                brand TEXT NOT NULL,
                model TEXT NOT NULL,
                fuel_type TEXT DEFAULT 'Petrol',
                current_km INTEGER DEFAULT 0,
                service_type TEXT NOT NULL, -- General Service, Periodic, Oil, AC, Brake, Body, Breakdown, On-Site
                complaint_description TEXT,
                preferred_date TEXT NOT NULL,
                preferred_time TEXT NOT NULL,
                pickup_required INTEGER DEFAULT 0,
                onsite_service INTEGER DEFAULT 0,
                address TEXT,
                image_urls TEXT,
                status TEXT DEFAULT 'CONFIRMED', -- PENDING, CONFIRMED, CHECKED_IN, CANCELLED
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO service_bookings VALUES(1,1,'SB-2026-00101',1,'Dr. K. Sridhar MBBS','9842722001','9842722001','TN-33-AB-1234','Hyundai','Creta SX(O)','Diesel',32450,'Periodic Maintenance','Engine oil service, minor brake squeal sound at low speed, full water wash','2026-10-01','09:30 AM',1,0,'12, Gandhiraj Road, Periyar Nagar, Erode',NULL,'CHECKED_IN','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO service_bookings VALUES(2,1,'SB-2026-00102',2,'Thiru. M. Shanmugam','9842722002','9842722002','TN-33-BV-5678','Tata Motors','Nexon XZ+ (O)','Petrol',48200,'AC & Electrical Service','AC cooling low during noon, cabin air filter replacement required','2026-10-01','11:00 AM',0,0,'104, Cauvery Road, Erode',NULL,'CHECKED_IN','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO service_bookings VALUES(3,1,'SB-2026-00103',3,'R. Anitha B.Tech','9842722003','9842722003','TN-33-CA-9988','Mahindra','XUV700 AX7 Luxury','Diesel',18600,'General Service & Inspection','20K Periodic service, wheel alignment & balancing, synthetic oil top up','2026-10-02','10:00 AM',0,0,'Sampath Nagar, Erode',NULL,'CONFIRMED','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO service_bookings VALUES(4,1,'SB-2026-08786',6,'mandy','8220718028','8220718028','TN 45 8545','Honda','activa','Petrol',30000,'General Service','break issue, oil service','2026-10-08','09:30 AM',0,0,NULL,NULL,'CONFIRMED','2026-10-08 02:11:04','2026-10-08 02:11:04');
CREATE TABLE vehicle_inspections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                vehicle_id INTEGER,
                customer_id INTEGER NOT NULL,
                reg_number TEXT NOT NULL,
                inspector_id INTEGER NOT NULL,
                km_reading INTEGER NOT NULL,
                fuel_level TEXT DEFAULT '1/2', -- E, 1/4, 1/2, 3/4, F
                tyre_condition TEXT DEFAULT 'Good',
                battery_condition TEXT DEFAULT 'Good',
                body_scratches_dents TEXT,
                accessories_found TEXT,
                customer_belongings TEXT,
                customer_complaints TEXT,
                customer_signature TEXT,
                photos TEXT,
                expected_delivery TEXT,
                created_at TEXT
            );
INSERT INTO vehicle_inspections VALUES(1,1,7,6,'TN 45 7584',1,2000,'1/2','Good','Weak','front mudguard damage',NULL,'','slow speed',NULL,NULL,'2026-10-08 05:00 PM','2026-10-08 02:35:07');
CREATE TABLE job_cards (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                job_card_number TEXT UNIQUE NOT NULL,
                booking_id INTEGER,
                inspection_id INTEGER,
                customer_id INTEGER NOT NULL,
                vehicle_id INTEGER NOT NULL,
                service_type TEXT NOT NULL,
                priority TEXT DEFAULT 'NORMAL', -- LOW, NORMAL, HIGH, URGENT
                status TEXT DEFAULT 'WAITING_INSPECTION',
                -- WAITING_INSPECTION, ESTIMATION, WAITING_APPROVAL, APPROVED, TECHNICIAN_ASSIGNED, WORK_IN_PROGRESS, WAITING_PARTS, QUALITY_CHECK, READY, DELIVERED, CANCELLED
                service_advisor_id INTEGER NOT NULL,
                technician_id INTEGER,
                promised_date TEXT,
                promised_time TEXT,
                internal_deadline TEXT,
                start_time TEXT,
                completion_time TEXT,
                delivery_time TEXT,
                delay_reason TEXT,
                estimated_parts_cost REAL DEFAULT 0.00,
                estimated_labour_cost REAL DEFAULT 0.00,
                estimated_total REAL DEFAULT 0.00,
                actual_parts_cost REAL DEFAULT 0.00,
                actual_labour_cost REAL DEFAULT 0.00,
                final_amount REAL DEFAULT 0.00,
                customer_approval_status TEXT DEFAULT 'PENDING', -- PENDING, APPROVED, REJECTED
                customer_approval_time TEXT,
                customer_signature TEXT,
                is_repeat_comeback INTEGER DEFAULT 0,
                previous_job_card_id INTEGER,
                technician_notes TEXT,
                qr_token TEXT,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO job_cards VALUES(1,1,'JOB-2026-000001',1,NULL,1,1,'Periodic Maintenance & Brakes','NORMAL','WORK_IN_PROGRESS',2,7,'2026-10-01','05:30 PM','2026-10-01 16:30:00','2026-10-01 10:00:00',NULL,NULL,NULL,4020.0,1200.0,5220.0,4020.0,1200.0,5220.0,'APPROVED','2026-10-01 10:30:00',NULL,0,NULL,'Oil drained, new filter installed. Front brake caliper cleaning ongoing.','QR-JOB-001','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_cards VALUES(2,1,'JOB-2026-000002',2,NULL,2,2,'AC Repair & Cooling Service','HIGH','QUALITY_CHECK',2,8,'2026-10-01','04:00 PM','2026-10-01 15:30:00','2026-10-01 11:15:00','2026-10-01 14:45:00',NULL,NULL,1640.0,950.0,2590.0,1640.0,950.0,2590.0,'APPROVED','2026-10-01 11:45:00',NULL,0,NULL,'AC gas topped up to 450g. Cabin filter replaced. Vent temp 6.5°C achieved.','QR-JOB-002','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_cards VALUES(3,1,'JOB-2026-000003',NULL,NULL,4,4,'Brake & Suspension Overhaul','URGENT','ESTIMATION',2,NULL,'2026-10-01','06:30 PM','2026-10-01 17:30:00',NULL,NULL,NULL,NULL,6750.0,1800.0,8550.0,0.0,0.0,8550.0,'PENDING',NULL,NULL,0,NULL,'Front right strut leaking oil. Brake disc skim recommended.','QR-JOB-003','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_cards VALUES(4,1,'JOB-2026-000004',NULL,NULL,5,5,'Clutch Replacement & Gearbox','HIGH','WAITING_PARTS',2,9,'2026-10-02','02:00 PM','2026-10-02 13:00:00','2026-10-01 09:00:00',NULL,NULL,'Waiting for OEM clutch release bearing from Chennai hub',5400.0,2200.0,7600.0,0.0,0.0,7600.0,'APPROVED','2026-10-01 09:45:00',NULL,0,NULL,'Gearbox unbolted. Waiting for bearing delivery at 3 PM.','QR-JOB-004','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_cards VALUES(5,1,'JOB-2026-000005',NULL,NULL,3,3,'General Service & Wash','NORMAL','DELIVERED',2,7,'2026-09-30','04:00 PM','2026-09-30 15:00:00','2026-09-30 10:00:00','2026-09-30 14:30:00','2026-09-30 16:30:00',NULL,3600.0,900.0,4500.0,3600.0,900.0,4500.0,'APPROVED','2026-09-30 10:30:00',NULL,0,NULL,'Service completed cleanly. Customer satisfied.','QR-JOB-005','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_cards VALUES(6,1,'JOB-2026-000006',NULL,1,6,7,'General Service','NORMAL','TECHNICIAN_ASSIGNED',1,7,'2026-10-08','05:00 PM',NULL,NULL,NULL,NULL,NULL,0.0,0.0,0.0,0.0,0.0,0.0,'PENDING',NULL,NULL,0,NULL,NULL,'QR-8c4fed91f75ff53d2d379f55d2f48c57','2026-10-08 02:35:07','2026-10-08 02:38:17');
CREATE TABLE job_card_complaints (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_id INTEGER NOT NULL,
                complaint TEXT NOT NULL,
                action_taken TEXT,
                status TEXT DEFAULT 'PENDING' -- PENDING, RESOLVED
            );
INSERT INTO job_card_complaints VALUES(1,1,'Periodic 30,000 KM Engine Oil & Filter Change','Oil drained and Mobil 1 5W40 + Bosch Filter added','RESOLVED');
INSERT INTO job_card_complaints VALUES(2,1,'Brake squeal sound while coming to halt','Ceramic brake pads inspected, pins greased','RESOLVED');
INSERT INTO job_card_complaints VALUES(3,2,'AC cooling ineffective in afternoon traffic','Leak test passed, R134a gas charged to 450g','RESOLVED');
INSERT INTO job_card_complaints VALUES(4,2,'Foul smell from AC vents when turned on','Cabin pollen filter replaced with Valeo Carbon filter','RESOLVED');
INSERT INTO job_card_complaints VALUES(5,3,'Steering vibration above 80 km/h and metal clunk on bumps','Right front strut leaking, recommended replacement','PENDING');
INSERT INTO job_card_complaints VALUES(6,4,'Clutch pedal hard and slippage in 3rd gear uphill','Clutch disc worn out, kit requisitioned','PENDING');
INSERT INTO job_card_complaints VALUES(7,6,'slow speed',NULL,'PENDING');
CREATE TABLE job_card_parts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_id INTEGER NOT NULL,
                part_id INTEGER NOT NULL,
                part_name TEXT NOT NULL,
                part_code TEXT NOT NULL,
                quantity INTEGER NOT NULL DEFAULT 1,
                unit_price REAL NOT NULL,
                gst_rate REAL DEFAULT 18.00,
                gst_amount REAL DEFAULT 0.00,
                total_amount REAL NOT NULL,
                status TEXT DEFAULT 'REQUESTED', -- REQUESTED, APPROVED, ISSUED, RETURNED
                issued_by INTEGER,
                issued_at TEXT,
                created_at TEXT
            );
INSERT INTO job_card_parts VALUES(1,1,1,'Mobil 1 Fully Synthetic Engine Oil 5W-40 (4L)','PRT-OIL-5W40-01',1,3600.0,18.0,648.0,4248.0,'ISSUED',6,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_card_parts VALUES(2,1,2,'Bosch Spin-on Heavy Duty Oil Filter','PRT-FLT-OIL-02',1,420.0,18.0,75.59999999999999431,495.6000000000000227,'ISSUED',6,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_card_parts VALUES(3,2,6,'Floron Pure R134a Eco AC Refrigerant Can (450g)','PRT-AC-GAS-06',1,750.0,18.0,135.0,885.0,'ISSUED',6,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_card_parts VALUES(4,2,7,'Valeo Activated Carbon Cabin AC Filter','PRT-AC-CAB-07',1,890.0,18.0,160.1999999999999887,1050.200000000000045,'ISSUED',6,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO job_card_parts VALUES(5,4,11,'Valeo Clutch Plate & Cover Assembly Kit','PRT-TRN-CLT-11',1,5400.0,18.0,972.0,6372.0,'APPROVED',6,NULL,'2026-10-01 11:12:51');
CREATE TABLE job_card_labour (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_id INTEGER NOT NULL,
                description TEXT NOT NULL,
                sac_code TEXT DEFAULT '998729',
                hours REAL DEFAULT 1.0,
                rate_per_hour REAL NOT NULL,
                gst_rate REAL DEFAULT 18.00,
                gst_amount REAL DEFAULT 0.00,
                total_amount REAL NOT NULL,
                technician_id INTEGER,
                created_at TEXT
            );
INSERT INTO job_card_labour VALUES(1,1,'Periodic Maintenance Service & Inspection Labour','998729',2.0,400.0,18.0,144.0,944.0,7,'2026-10-01 11:12:51');
INSERT INTO job_card_labour VALUES(2,1,'Front Brake Caliper Servicing & Pin Greasing','998729',1.0,400.0,18.0,72.0,472.0,7,'2026-10-01 11:12:51');
INSERT INTO job_card_labour VALUES(3,2,'AC System Diagnostic, Vacuum & Gas Recharging Labour','998729',1.5,500.0,18.0,135.0,885.0,8,'2026-10-01 11:12:51');
INSERT INTO job_card_labour VALUES(4,2,'Cabin Filter Cleaning & Duct Sanitization','998729',0.5,400.0,18.0,36.0,236.0,8,'2026-10-01 11:12:51');
CREATE TABLE job_status_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_id INTEGER NOT NULL,
                old_status TEXT,
                new_status TEXT NOT NULL,
                changed_by INTEGER NOT NULL,
                notes TEXT,
                created_at TEXT
            );
INSERT INTO job_status_history VALUES(1,6,NULL,'WAITING_INSPECTION',1,'Vehicle checked in and digital inspection completed','2026-10-08 02:35:07');
INSERT INTO job_status_history VALUES(2,6,'APPROVED','TECHNICIAN_ASSIGNED',1,'Technician assigned to job','2026-10-08 02:38:17');
CREATE TABLE service_estimates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                estimate_number TEXT UNIQUE NOT NULL,
                job_card_id INTEGER NOT NULL,
                customer_id INTEGER NOT NULL,
                parts_subtotal REAL DEFAULT 0.00,
                labour_subtotal REAL DEFAULT 0.00,
                tax_amount REAL DEFAULT 0.00,
                discount_amount REAL DEFAULT 0.00,
                grand_total REAL NOT NULL,
                status TEXT DEFAULT 'SENT', -- DRAFT, SENT, APPROVED, REJECTED, EXPIRED
                approval_token TEXT UNIQUE,
                approved_at TEXT,
                rejection_reason TEXT,
                valid_until TEXT,
                created_by INTEGER NOT NULL,
                created_at TEXT,
                updated_at TEXT
            );
CREATE TABLE estimate_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                estimate_id INTEGER NOT NULL,
                item_type TEXT NOT NULL, -- 'PART' or 'LABOUR'
                part_id INTEGER,
                name TEXT NOT NULL,
                quantity INTEGER DEFAULT 1,
                unit_price REAL NOT NULL,
                tax_rate REAL DEFAULT 18.00,
                tax_amount REAL DEFAULT 0.00,
                total_amount REAL NOT NULL
            );
CREATE TABLE quality_checks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_id INTEGER NOT NULL,
                inspector_id INTEGER NOT NULL,
                complaints_resolved INTEGER DEFAULT 1,
                parts_fitted_properly INTEGER DEFAULT 1,
                road_test_passed INTEGER DEFAULT 1,
                oil_fluids_checked INTEGER DEFAULT 1,
                electricals_tested INTEGER DEFAULT 1,
                vehicle_cleaned INTEGER DEFAULT 1,
                belongings_verified INTEGER DEFAULT 1,
                overall_status TEXT DEFAULT 'PASSED', -- PASSED, REJECTED
                notes TEXT,
                checked_at TEXT
            );
CREATE TABLE customer_feedbacks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_id INTEGER NOT NULL,
                customer_id INTEGER NOT NULL,
                service_rating INTEGER NOT NULL, -- 1 to 5
                staff_rating INTEGER NOT NULL,
                timing_rating INTEGER NOT NULL,
                cleanliness_rating INTEGER NOT NULL,
                overall_rating INTEGER NOT NULL,
                comments TEXT,
                created_at TEXT
            );
CREATE TABLE part_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                code TEXT NOT NULL UNIQUE,
                is_active INTEGER DEFAULT 1
            );
INSERT INTO part_categories VALUES(1,'Engine & Powertrain','ENG',1);
INSERT INTO part_categories VALUES(2,'Brake & Friction','BRK',1);
INSERT INTO part_categories VALUES(3,'Suspension & Steering','SUS',1);
INSERT INTO part_categories VALUES(4,'Electrical & Electronics','ELE',1);
INSERT INTO part_categories VALUES(5,'Air Conditioning & Cooling','AC',1);
INSERT INTO part_categories VALUES(6,'Oils, Fluids & Lubricants','OIL',1);
INSERT INTO part_categories VALUES(7,'Filters & Maintenance Kits','FLT',1);
INSERT INTO part_categories VALUES(8,'Body, Glass & Lights','BDY',1);
INSERT INTO part_categories VALUES(9,'Transmission & Clutch','TRN',1);
INSERT INTO part_categories VALUES(10,'Tyres, Wheels & Alignment','TYR',1);
CREATE TABLE parts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                category_id INTEGER NOT NULL,
                part_code TEXT UNIQUE NOT NULL,
                barcode TEXT UNIQUE,
                part_name TEXT NOT NULL,
                brand TEXT NOT NULL,
                compatible_models TEXT,
                oem_number TEXT,
                hsn_code TEXT DEFAULT '8708',
                supplier_id INTEGER,
                purchase_cost REAL NOT NULL,
                dealer_cost REAL NOT NULL,
                selling_price REAL NOT NULL,
                mrp REAL NOT NULL,
                gst_rate REAL DEFAULT 18.00,
                current_stock INTEGER DEFAULT 0,
                min_stock_level INTEGER DEFAULT 5,
                max_stock_level INTEGER DEFAULT 100,
                rack_location TEXT DEFAULT 'A-01',
                condition TEXT DEFAULT 'GOOD_STOCK', -- GOOD_STOCK, DAMAGED, RETURN, SCRAP, RESERVED, ISSUED
                status TEXT DEFAULT 'active',
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO parts VALUES(1,1,6,'PRT-OIL-5W40-01','890123400101','Mobil 1 Fully Synthetic Engine Oil 5W-40 (4L)','Mobil 1','Universal (Hyundai, Tata, Mahindra, Honda)','OEM-MOB-5W40-4L','27101981',NULL,2200.0,2600.0,3600.0,4200.0,18.0,45,10,100,'RACK-OIL-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(2,1,7,'PRT-FLT-OIL-02','890123400102','Bosch Spin-on Heavy Duty Oil Filter','Bosch','Hyundai Creta, Venue, Verna','OEM-HYU-26300','84212300',NULL,210.0,260.0,420.0,480.0,18.0,60,15,120,'RACK-FLT-02','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(3,1,7,'PRT-FLT-AIR-03','890123400103','Mahle High Airflow Engine Air Filter','Mahle','Tata Nexon / Altroz Revotron','OEM-TAT-54020','84213100',NULL,320.0,390.0,650.0,750.0,18.0,32,10,80,'RACK-FLT-03','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(4,1,2,'PRT-BRK-PAD-04','890123400104','TVS Girling Front Ceramic Brake Pads (Set of 4)','TVS Girling','Mahindra XUV700 / Scorpio-N','OEM-MAH-BRK-09','87083000',NULL,1650.0,1950.0,2950.0,3400.0,18.0,18,6,50,'RACK-BRK-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(5,1,2,'PRT-BRK-FLUID-05','890123400105','Castrol DOT 4 High Temp Brake Fluid (500ml)','Castrol','Universal','OEM-CAS-DOT4-500','38190010',NULL,140.0,175.0,280.0,330.0,18.0,50,12,100,'RACK-BRK-02','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(6,1,5,'PRT-AC-GAS-06','890123400106','Floron Pure R134a Eco AC Refrigerant Can (450g)','Floron','Universal Auto AC','OEM-FLR-R134A','29033990',NULL,380.0,450.0,750.0,890.0,18.0,24,8,60,'RACK-AC-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(7,1,5,'PRT-AC-CAB-07','890123400107','Valeo Activated Carbon Cabin AC Filter','Valeo','Toyota Innova Crysta / Hycross','OEM-TOY-87139','84213920',NULL,450.0,550.0,890.0,1050.0,18.0,4,8,40,'RACK-AC-02','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(8,1,4,'PRT-ELE-BAT-08','890123400108','Exide Epiq 55Ah Maintenance Free Auto Battery','Exide','Tata Harrier, Safari, XUV700','OEM-EXD-EP55D','85071000',NULL,4400.0,5100.0,6800.0,7600.0,28.0,8,5,25,'RACK-BAT-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(9,1,4,'PRT-ELE-SPK-09','890123400109','NGK Laser Iridium Long-Life Spark Plugs (4-Pack)','NGK','Honda City, Civic, Swift DualJet','OEM-NGK-ILZKR7','85111000',NULL,1200.0,1450.0,2200.0,2600.0,18.0,2,5,30,'RACK-ELE-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(10,1,3,'PRT-SUS-SHK-10','890123400110','Gabriel Gas Shock Absorber Front Assembly','Gabriel','Hyundai Creta / Kia Seltos','OEM-GAB-54650','87088000',NULL,2100.0,2500.0,3800.0,4400.0,18.0,12,4,30,'RACK-SUS-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(11,1,9,'PRT-TRN-CLT-11','890123400111','Valeo Clutch Plate & Cover Assembly Kit','Valeo','Maruti Swift / Dzire Diesel','OEM-VAL-CLT-03','87089300',NULL,3100.0,3650.0,5400.0,6200.0,18.0,7,3,20,'RACK-TRN-01','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO parts VALUES(12,1,6,'PRT-OIL-COOL-12','890123400112','Gulf Long-Life Premix Radiator Coolant 1L','Gulf','Universal Cooling Systems','OEM-GLF-CLN-1L','38200000',NULL,120.0,150.0,240.0,290.0,18.0,40,10,80,'RACK-OIL-02','GOOD_STOCK','active','2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE stock_transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                part_id INTEGER NOT NULL,
                transaction_type TEXT NOT NULL, -- PURCHASE, ISSUE_JOBCARD, RETURN, ADJUSTMENT, TRANSFER_IN, TRANSFER_OUT
                reference_type TEXT, -- 'JOB_CARD', 'PURCHASE', 'MANUAL'
                reference_id INTEGER,
                quantity INTEGER NOT NULL, -- positive or negative
                unit_cost REAL NOT NULL,
                total_value REAL NOT NULL,
                balance_after INTEGER NOT NULL,
                notes TEXT,
                user_id INTEGER NOT NULL,
                created_at TEXT
            );
CREATE TABLE branch_transfers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                transfer_number TEXT UNIQUE NOT NULL,
                transfer_type TEXT NOT NULL, -- 'SPARE_PART' or 'VEHICLE'
                from_branch_id INTEGER NOT NULL,
                to_branch_id INTEGER NOT NULL,
                item_id INTEGER NOT NULL,
                quantity INTEGER DEFAULT 1,
                status TEXT DEFAULT 'COMPLETED', -- PENDING, IN_TRANSIT, COMPLETED, CANCELLED
                notes TEXT,
                initiated_by INTEGER NOT NULL,
                created_at TEXT
            );
CREATE TABLE suppliers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                supplier_code TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                contact_person TEXT,
                mobile TEXT NOT NULL,
                email TEXT,
                address TEXT,
                city TEXT DEFAULT 'Erode',
                gstin TEXT,
                pan TEXT,
                bank_name TEXT,
                account_number TEXT,
                ifsc_code TEXT,
                outstanding_balance REAL DEFAULT 0.00,
                is_active INTEGER DEFAULT 1,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO suppliers VALUES(1,'SUP-001','Bosch Automotive India Ltd','S. Ramesh','9842755001','ramesh@bosch-auto.in','34/2, Industrial Estate, SIDCO','Erode','33AAACB2233M1Z2','AAACB2233M',NULL,NULL,NULL,0.0,1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO suppliers VALUES(2,'SUP-002','Lucas TVS Auto Components','K. Murugesan','9842755002','murugesan@lucastvs.com','12, Mettur Road, Near Bus Stand','Erode','33AAACL5566K1Z9','AAACL5566K',NULL,NULL,NULL,4200.0,1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO suppliers VALUES(3,'SUP-003','Mobil Lubricants Distributorship','V. Anand','9842755003','anand@mobildist.com','78, Perundurai Road','Erode','33AABBM8899P1Z4','AABBM8899P',NULL,NULL,NULL,0.0,1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO suppliers VALUES(4,'SUP-004','Exide Energy Solutions Ltd','N. Kumar','9842755004','kumar@exide.in','55, Sathy Road','Erode','33AABBE1122R1Z1','AABBE1122R',NULL,NULL,NULL,12500.0,1,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO suppliers VALUES(5,'SUP-205','manikanda enterprises','manikanda','8220718028','mani@mani','test','Erode','54545456456454564241',NULL,NULL,NULL,NULL,0.0,1,'2026-10-08 08:11:29','2026-10-08 08:11:29');
CREATE TABLE purchase_orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                po_number TEXT UNIQUE NOT NULL,
                supplier_id INTEGER NOT NULL,
                po_date TEXT NOT NULL,
                expected_date TEXT,
                subtotal REAL NOT NULL,
                tax_amount REAL DEFAULT 0.00,
                total_amount REAL NOT NULL,
                status TEXT DEFAULT 'PENDING', -- PENDING, RECEIVED, PARTIAL, CANCELLED
                created_by INTEGER NOT NULL,
                created_at TEXT,
                updated_at TEXT
            );
CREATE TABLE purchases (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                purchase_invoice_no TEXT NOT NULL,
                supplier_id INTEGER NOT NULL,
                po_id INTEGER,
                purchase_date TEXT NOT NULL,
                subtotal REAL NOT NULL,
                tax_amount REAL DEFAULT 0.00,
                round_off REAL DEFAULT 0.00,
                grand_total REAL NOT NULL,
                paid_amount REAL DEFAULT 0.00,
                outstanding_amount REAL DEFAULT 0.00,
                payment_status TEXT DEFAULT 'PAID', -- UNPAID, PARTIAL, PAID
                created_by INTEGER NOT NULL,
                created_at TEXT,
                updated_at TEXT
            );
CREATE TABLE purchase_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                purchase_id INTEGER NOT NULL,
                part_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL,
                unit_price REAL NOT NULL,
                tax_rate REAL DEFAULT 18.00,
                tax_amount REAL DEFAULT 0.00,
                total_amount REAL NOT NULL
            );
CREATE TABLE invoices (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                invoice_number TEXT UNIQUE NOT NULL,
                invoice_type TEXT NOT NULL, -- 'GARAGE_SERVICE', 'VEHICLE_SALE', 'SPARE_SALE'
                customer_id INTEGER NOT NULL,
                job_card_id INTEGER,
                vehicle_sale_id INTEGER,
                invoice_date TEXT NOT NULL,
                due_date TEXT,
                subtotal REAL NOT NULL,
                parts_total REAL DEFAULT 0.00,
                labour_total REAL DEFAULT 0.00,
                discount_amount REAL DEFAULT 0.00,
                cgst_amount REAL DEFAULT 0.00,
                sgst_amount REAL DEFAULT 0.00,
                igst_amount REAL DEFAULT 0.00,
                tax_amount REAL DEFAULT 0.00,
                round_off REAL DEFAULT 0.00,
                grand_total REAL NOT NULL,
                paid_amount REAL DEFAULT 0.00,
                outstanding_amount REAL DEFAULT 0.00,
                payment_status TEXT DEFAULT 'UNPAID', -- UNPAID, PARTIAL, PAID
                is_gst INTEGER DEFAULT 1,
                upi_qr_string TEXT,
                notes TEXT,
                created_by INTEGER NOT NULL,
                created_at TEXT,
                updated_at TEXT
            );
INSERT INTO invoices VALUES(1,1,'INV-2026-000088','GARAGE_SERVICE',3,5,NULL,'2026-09-30','2026-09-30',4500.0,3600.0,900.0,0.0,405.0,405.0,0.0,810.0,0.0,5310.0,5310.0,0.0,'PAID',1,'upi://pay?pa=erodeauto@upi&pn=ErodeAutomobiles&am=5310.00&tr=INV2026000088','General Service & Wash Bill',11,'2026-10-01 11:12:51','2026-10-01 11:12:51');
INSERT INTO invoices VALUES(2,1,'INV-2026-000089','SPARE_SALE',2,NULL,NULL,'2026-10-01','2026-10-01',3800.0,3800.0,0.0,100.0,333.0,333.0,0.0,666.0,0.0,4366.0,2000.0,2366.0,'PARTIAL',1,'upi://pay?pa=erodeauto@upi&pn=ErodeAutomobiles&am=2366.00&tr=INV2026000089','Counter Sale of Gabriel Shock Absorbers',11,'2026-10-01 11:12:51','2026-10-01 11:12:51');
CREATE TABLE invoice_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                invoice_id INTEGER NOT NULL,
                item_type TEXT NOT NULL, -- 'PART', 'LABOUR', 'VEHICLE', 'OTHER'
                item_id INTEGER,
                item_name TEXT NOT NULL,
                hsn_sac TEXT,
                quantity REAL DEFAULT 1.0,
                unit_price REAL NOT NULL,
                discount REAL DEFAULT 0.00,
                taxable_value REAL NOT NULL,
                gst_rate REAL DEFAULT 18.00,
                cgst REAL DEFAULT 0.00,
                sgst REAL DEFAULT 0.00,
                igst REAL DEFAULT 0.00,
                total_amount REAL NOT NULL
            );
CREATE TABLE payments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                payment_number TEXT UNIQUE NOT NULL,
                invoice_id INTEGER,
                customer_id INTEGER,
                supplier_id INTEGER,
                payment_type TEXT NOT NULL, -- 'RECEIPT' (Customer), 'PAYMENT' (Supplier), 'EXPENSE'
                payment_mode TEXT NOT NULL, -- Cash, UPI, Card, Bank Transfer, Cheque, Finance
                amount REAL NOT NULL,
                reference_number TEXT,
                payment_date TEXT NOT NULL,
                notes TEXT,
                received_by INTEGER NOT NULL,
                created_at TEXT
            );
CREATE TABLE accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                account_code TEXT UNIQUE NOT NULL,
                account_name TEXT NOT NULL,
                account_type TEXT NOT NULL, -- ASSET, LIABILITY, EQUITY, INCOME, EXPENSE
                sub_type TEXT, -- CASH, BANK, RECEIVABLE, PAYABLE, DIRECT_EXPENSE, INDIRECT_EXPENSE, SALES
                opening_balance REAL DEFAULT 0.00,
                current_balance REAL DEFAULT 0.00,
                is_active INTEGER DEFAULT 1,
                created_at TEXT
            );
INSERT INTO accounts VALUES(1,1,'1010','Cash in Hand (Main Counter)','ASSET','CASH',25000.0,38450.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(2,1,'1020','State Bank of India Current A/c','ASSET','BANK',450000.0,892100.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(3,1,'1030','HDFC Bank Auto Collections A/c','ASSET','BANK',200000.0,415000.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(4,1,'1040','Sundry Debtors (Customer Receivables)','ASSET','RECEIVABLE',45000.0,10950.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(5,1,'1050','Vehicle Stock Asset','ASSET','STOCK',9800000.0,9338000.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(6,1,'1060','Spare Parts Stock Asset','ASSET','STOCK',450000.0,498200.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(7,1,'2010','Sundry Creditors (Supplier Payables)','LIABILITY','PAYABLE',80000.0,16700.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(8,1,'2020','Output GST Payable (CGST + SGST)','LIABILITY','TAX',35000.0,42300.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(9,1,'3010','Owner Capital & Reserves','EQUITY','CAPITAL',5000000.0,5000000.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(10,1,'4010','Automobile Sales Revenue','INCOME','SALES',0.0,1850000.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(11,1,'4020','Garage Labour Service Revenue','INCOME','SERVICE',0.0,184500.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(12,1,'4030','Spare Parts Sales Revenue','INCOME','SALES',0.0,245600.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(13,1,'5010','Workshop Rent & Maintenance','EXPENSE','INDIRECT_EXPENSE',0.0,45000.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(14,1,'5020','Electricity & Power Charges','EXPENSE','INDIRECT_EXPENSE',0.0,14200.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(15,1,'5030','Technician & Staff Salaries','EXPENSE','DIRECT_EXPENSE',0.0,125000.0,1,'2026-10-01 11:12:51');
INSERT INTO accounts VALUES(16,1,'5040','Workshop Consumables & Tools','EXPENSE','DIRECT_EXPENSE',0.0,12800.0,1,'2026-10-01 11:12:51');
CREATE TABLE ledger_entries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                account_id INTEGER NOT NULL,
                transaction_date TEXT NOT NULL,
                voucher_type TEXT NOT NULL, -- SALES, PURCHASE, RECEIPT, PAYMENT, JOURNAL, CONTRA
                voucher_no TEXT NOT NULL,
                debit REAL DEFAULT 0.00,
                credit REAL DEFAULT 0.00,
                balance REAL DEFAULT 0.00,
                narration TEXT,
                created_by INTEGER NOT NULL,
                created_at TEXT
            );
CREATE TABLE expenses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                expense_number TEXT UNIQUE NOT NULL,
                category TEXT NOT NULL, -- Rent, Electricity, Salaries, Workshop Consumables, Tea/Snacks, Marketing, Tools Repair, Misc
                amount REAL NOT NULL,
                payment_mode TEXT DEFAULT 'Cash',
                expense_date TEXT NOT NULL,
                payee_name TEXT,
                invoice_ref TEXT,
                notes TEXT,
                approved_by INTEGER,
                created_by INTEGER NOT NULL,
                created_at TEXT
            );
INSERT INTO expenses VALUES(1,1,'EXP-2026-001','Workshop Consumables',2400.0,'Cash','2026-10-01','Sri Balaji Hardware Erode','INV-SB-992','Sanding papers, cotton waste, degreaser chemical cans',1,10,'2026-10-01 11:12:51');
INSERT INTO expenses VALUES(2,1,'EXP-2026-002','Tea & Refreshments',650.0,'Cash','2026-10-01','Saravana Tea Stall','DAILY-TEA-01','Staff and customer welcome refreshments',1,10,'2026-10-01 11:12:51');
INSERT INTO expenses VALUES(3,1,'EXP-2026-003','Tools Repair',1800.0,'UPI','2026-09-29','Precision Hydraulics Erode','REC-PH-331','Two-post lift safety valve oil seal replacement',1,10,'2026-10-01 11:12:51');
CREATE TABLE notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                branch_id INTEGER DEFAULT 1,
                title TEXT NOT NULL,
                message TEXT NOT NULL,
                type TEXT DEFAULT 'info', -- info, warning, danger, success
                link TEXT,
                is_read INTEGER DEFAULT 0,
                created_at TEXT
            );
INSERT INTO notifications VALUES(1,NULL,1,'Critical Stock Alert','Part PRT-ELE-SPK-09 (NGK Laser Iridium Spark Plugs) has only 2 items left (Min: 5). Suggested reorder: 25 items.','danger','/inventory/low-stock',0,'2026-10-01 11:12:51');
INSERT INTO notifications VALUES(2,NULL,1,'Service Quality Passed','Job Card JOB-2026-000002 (TN-33-BV-5678 Tata Nexon) passed 7-point quality test. Ready for delivery.','success','/garage/job-cards/2',0,'2026-10-01 11:12:51');
INSERT INTO notifications VALUES(3,NULL,1,'New Service Booking','Customer Dr. K. Sridhar booked Periodic Service for TN-33-AB-1234 via Web Portal.','info','/garage/checkin',1,'2026-10-01 11:12:51');
INSERT INTO notifications VALUES(4,NULL,1,'Estimate Awaiting Approval','Estimate for JOB-2026-000003 (₹8,550.00) sent to Advocate Balasubramaniam via WhatsApp.','warning','/garage/job-cards/3',0,'2026-10-01 11:12:51');
INSERT INTO notifications VALUES(5,NULL,1,'New Web Service Booking','New online booking SB-2026-08786 received from mandy for TN 45 8545 (General Service).','info','/garage/checkin',0,'2026-10-08 02:11:04');
CREATE TABLE whatsapp_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                recipient_mobile TEXT NOT NULL,
                template_name TEXT NOT NULL,
                message_body TEXT NOT NULL,
                status TEXT DEFAULT 'SENT', -- SENT, DELIVERED, FAILED
                response_payload TEXT,
                created_at TEXT
            );
INSERT INTO whatsapp_logs VALUES(1,1,'8220718028','booking_confirmed',unistr('🚗 *ERODE AUTOMOBILES*\u000a\u000aDear mandy,\u000aYour service booking for *TN 45 8545* has been confirmed.\u000a\u000a📅 Date: 2026-10-08 at 09:30 AM\u000a🔖 Booking Ref: SB-2026-08786\u000a\u000aWe look forward to serving you!\u000a📞 Helpdesk: +91 98427 12345'),'SENT',NULL,'2026-10-08 02:11:04');
INSERT INTO whatsapp_logs VALUES(2,1,'124545645656','inspection_complete',unistr('🔍 *ERODE AUTOMOBILES - Vehicle Inspection Complete*\u000a\u000aDear mandy,\u000aYour vehicle *TN 45 7584* (Job: JOB-2026-000006) has been inspected by our floor engineer.\u000a\u000aOur service manager is preparing your detailed digital estimate.\u000aTrack live status: http://localhost:8000/track?q=JOB-2026-000006'),'SENT',NULL,'2026-10-08 02:35:07');
CREATE TABLE sms_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                branch_id INTEGER DEFAULT 1,
                mobile TEXT NOT NULL,
                message TEXT NOT NULL,
                status TEXT DEFAULT 'SENT',
                created_at TEXT
            );
CREATE TABLE service_reminders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                customer_id INTEGER NOT NULL,
                vehicle_id INTEGER NOT NULL,
                reminder_type TEXT NOT NULL, -- Next Service, Oil Replacement, Insurance Renewal, PUC Expiry, Warranty
                due_date TEXT NOT NULL,
                status TEXT DEFAULT 'PENDING', -- PENDING, SENT, COMPLETED, CANCELLED
                sent_at TEXT,
                notes TEXT,
                created_at TEXT
            );
INSERT INTO service_reminders VALUES(1,1,1,'Next Service','2026-10-08','PENDING',NULL,'Scheduled 40,000 KM major maintenance','2026-10-01 11:12:51');
INSERT INTO service_reminders VALUES(2,2,2,'Insurance Renewal','2026-10-16','PENDING',NULL,'United India Insurance policy expiring soon','2026-10-01 11:12:51');
INSERT INTO service_reminders VALUES(3,4,4,'PUC Expiry','2026-10-04','PENDING',NULL,'Emission certificate due for renewal','2026-10-01 11:12:51');
INSERT INTO service_reminders VALUES(4,5,5,'Oil Replacement','2026-10-21','PENDING',NULL,'10,000 KM engine oil replacement reminder','2026-10-01 11:12:51');
CREATE TABLE settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                key_name TEXT UNIQUE NOT NULL,
                key_value TEXT,
                created_at TEXT
            );
CREATE TABLE mfg_vehicle_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                code TEXT NOT NULL UNIQUE,
                description TEXT,
                is_active INTEGER DEFAULT 1,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_vehicle_categories VALUES(1,'Electric Two Wheeler (2W)','EV-2W','High-efficiency urban commuter & performance electric scooters',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_categories VALUES(2,'Electric Motorcycle (2W-M)','EV-BIKE','Sport & naked street electric motorcycles with swappable/fixed battery packs',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_categories VALUES(3,'Electric Three Wheeler (3W-Cargo)','EV-3W-CARGO','Commercial last-mile logistics and cargo delivery EV vehicles',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_categories VALUES(4,'Custom Built EV Special','EV-CUSTOM','Tailored custom built EV chassis for fleet, racing, or special operations',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_categories VALUES(5,'Retro Vintage Conversion Kit','EV-RETRO','Electric powertrain retrofitting kits and completed retro cafe-racers',1,'2026-10-08 02:29:36');
CREATE TABLE mfg_vehicle_models (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                category_id INTEGER NOT NULL,
                model_code TEXT NOT NULL UNIQUE,
                model_name TEXT NOT NULL,
                vehicle_type TEXT DEFAULT 'ELECTRIC_2W',
                base_price REAL DEFAULT 0.00,
                std_build_days INTEGER DEFAULT 3,
                is_custom INTEGER DEFAULT 0,
                specifications TEXT,
                is_active INTEGER DEFAULT 1,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_vehicle_models VALUES(1,1,'E1-URBAN','Erode Aero E1 Smart Scooter','ELECTRIC_2W',89000.0,2,0,'{"top_speed":"75 km\/h","range":"110 km","brakes":"Combi-Disc","gradeability":"15 deg","battery":"2.8 kWh LiFePO4"}',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_models VALUES(2,1,'E1-PRO','Erode Aero E1 Pro High-Range','ELECTRIC_2W',114000.0,3,0,'{"top_speed":"90 km\/h","range":"160 km","brakes":"Dual Channel ABS","gradeability":"18 deg","battery":"3.8 kWh NMC"}',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_models VALUES(3,2,'THUNDER-300','Erode Thunder Pro 3.0 Sport Bike','ELECTRIC_2W_M',145000.0,4,0,'{"top_speed":"120 km\/h","range":"180 km","brakes":"Dual Disc with Bosch ABS","battery":"5.0 kWh High Discharge"}',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_models VALUES(4,3,'CARGO-MAX-750','Erode Cargo Hauler Max L5N','ELECTRIC_3W_CARGO',235000.0,5,0,'{"payload":"750 kg","range":"130 km","cargo_box_cuft":"140","motor":"8 kW Peak PMSM"}',1,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_models VALUES(5,4,'FALCON-GT','Erode Falcon GT Custom Performance','ELECTRIC_CUSTOM',195000.0,7,1,'{"custom_tuned":true,"top_speed":"135 km\/h","acceleration":"0-60 in 3.2s","suspension":"Inverted Upside Down Forks"}',1,'2026-10-08 02:29:36');
CREATE TABLE mfg_vehicle_variants (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                model_id INTEGER NOT NULL,
                variant_name TEXT NOT NULL,
                battery_capacity TEXT,
                motor_rating TEXT,
                payload_kg INTEGER DEFAULT 150,
                range_km INTEGER DEFAULT 120,
                top_speed_kmph INTEGER DEFAULT 85,
                color_options TEXT DEFAULT 'Pearl White, Matte Black, Racing Red, Metallic Blue',
                ex_factory_price REAL DEFAULT 0.00,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_vehicle_variants VALUES(1,1,'Eco 60 Hub-Drive','2.2 kWh','2.5 kW',150,85,65,'Pearl White, Matte Black, Racing Red, Metallic Blue',82000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(2,1,'Standard 110 Smart','2.8 kWh','3.5 kW',160,110,75,'Pearl White, Matte Black, Racing Red, Metallic Blue',89000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(3,2,'Pro Long Range 160','3.8 kWh','4.8 kW',160,160,90,'Pearl White, Matte Black, Racing Red, Metallic Blue',114000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(4,3,'Thunder Standard Street','4.2 kWh','6.0 kW',180,140,105,'Pearl White, Matte Black, Racing Red, Metallic Blue',135000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(5,3,'Thunder Track Performance','5.0 kWh','8.5 kW',180,180,120,'Pearl White, Matte Black, Racing Red, Metallic Blue',155000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(6,4,'Cargo Max Closed Container','7.5 kWh','8.0 kW',750,130,55,'Pearl White, Matte Black, Racing Red, Metallic Blue',235000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(7,4,'Cargo Max Open Tipper Bed','7.5 kWh','8.0 kW',800,125,50,'Pearl White, Matte Black, Racing Red, Metallic Blue',242000.0,'2026-10-08 02:29:36');
INSERT INTO mfg_vehicle_variants VALUES(8,5,'Falcon GT Titanium Edition','5.5 kWh','10.0 kW',170,175,135,'Pearl White, Matte Black, Racing Red, Metallic Blue',195000.0,'2026-10-08 02:29:36');
CREATE TABLE mfg_raw_material_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                code TEXT NOT NULL UNIQUE,
                description TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_raw_material_categories VALUES(1,'Battery Packs & BMS Units','RMC-BATT','Lithium ion/LFP battery modules, smart BMS and wire taps','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(2,'Electric Motors & Controllers','RMC-MOTOR','BLDC hub motors, mid-drive PMSM motors and vector sine-wave controllers','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(3,'Chassis, Frames & Substructures','RMC-FRAME','Seamless tubular steel frames, swingarms, subframes and engine mounts','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(4,'Body Panels & ABS Moldings','RMC-BODY','Virgin grade ABS fairings, mudguards, side covers, windscreens','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(5,'Wheels, Tyres & Axles','RMC-WHEEL','Alloy rims, tubeless radial tyres, bearings, axles and spacers','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(6,'Suspension & Steering','RMC-SUSP','Front telescopic hydraulic forks, rear gas-charged mono-shocks, handle bars','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(7,'Braking Systems & Hydraulics','RMC-BRAKE','Hydraulic master cylinders, calipers, stainless steel disc rotors, brake lines','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(8,'Electrical Harness & Lighting','RMC-ELEC','Automotive grade main wiring harnesses, LED projector headlamps, tail lamps, indicators','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(9,'Display Clusters & Telematics (IoT)','RMC-IOT','7-inch digital TFT touch screens, GPS/GSM telemetry modules, RFID keys','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(10,'Hardware, Fasteners & Sealants','RMC-HARDWARE','Grade 8.8/10.9 high tensile bolts, nyloc nuts, rubber grommets, silicone sealants','2026-10-08 02:29:36');
INSERT INTO mfg_raw_material_categories VALUES(11,'Automotive Paints & Coatings','RMC-PAINT','Epoxy primers, metallic polyurethane basecoats, 2K high-gloss clear coats','2026-10-08 02:29:36');
CREATE TABLE mfg_raw_materials (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                category_id INTEGER NOT NULL,
                material_code TEXT NOT NULL UNIQUE,
                material_name TEXT NOT NULL,
                uom TEXT DEFAULT 'PCS',
                hsn_code TEXT DEFAULT '8714',
                gst_rate REAL DEFAULT 18.00,
                current_stock REAL DEFAULT 0.00,
                min_stock_level REAL DEFAULT 10.00,
                max_stock_level REAL DEFAULT 500.00,
                reorder_level REAL DEFAULT 20.00,
                avg_unit_cost REAL DEFAULT 0.00,
                preferred_supplier_id INTEGER,
                rack_location TEXT DEFAULT 'RACK-MFG-01',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_raw_materials VALUES(1,1,'RM-BATT-28','LiFePO4 2.8 kWh Battery Pack 60V 46Ah with CAN BMS','PCS','8507',18.0,85.0,20.0,200.0,30.0,26500.0,NULL,'BAY-BATT-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(2,1,'RM-BATT-38','NMC 3.8 kWh High Density Battery Pack 72V 52Ah with Smart BMS','PCS','8507',18.0,60.0,15.0,150.0,25.0,38000.0,NULL,'BAY-BATT-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(3,1,'RM-BATT-50','High Output 5.0 kWh Battery Pack 72V 70Ah Liquid-Cool Ready','PCS','8507',18.0,35.0,10.0,100.0,15.0,52000.0,NULL,'BAY-BATT-03','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(4,2,'RM-MOT-HUB35','3.5 kW Waterproof BLDC Hub Motor with Integrated Drum/Disc Flange','PCS','8501',18.0,90.0,25.0,250.0,40.0,8500.0,NULL,'BAY-MOT-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(5,2,'RM-MOT-MID48','4.8 kW High-Torque PMSM Mid-Drive Motor with Reduction Gearbox','PCS','8501',18.0,50.0,15.0,120.0,20.0,14500.0,NULL,'BAY-MOT-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(6,2,'RM-CTRL-FOC','FOC Vector Sine-Wave Smart Motor Controller 72V 150A Peak','PCS','8504',18.0,110.0,30.0,300.0,50.0,5800.0,NULL,'BAY-ELEC-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(7,3,'RM-FRM-E1','Robotic-Welded Tubular High-Tensile Steel Main Frame (E1)','PCS','8714',18.0,75.0,20.0,150.0,30.0,4800.0,NULL,'BAY-FRM-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(8,3,'RM-FRM-THUNDER','Trellis High-Grade Chromoly Steel Chassis (Thunder 300)','PCS','8714',18.0,40.0,10.0,100.0,15.0,7200.0,NULL,'BAY-FRM-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(9,3,'RM-SWINGARM-AL','Cast Aluminum Alloy Swingarm with Needle Roller Bearings','PCS','8714',18.0,80.0,20.0,200.0,35.0,2400.0,NULL,'BAY-FRM-03','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(10,4,'RM-BODY-E1-SET','Complete ABS Body Fairing Kit (Unpainted Set - 9 pcs) for E1','SET','3926',18.0,65.0,20.0,150.0,25.0,3900.0,NULL,'BAY-PLAST-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(11,4,'RM-BODY-TH-SET','Aerodynamic Tank & Tail Cowl Fairing Set for Thunder 300','SET','3926',18.0,45.0,15.0,100.0,20.0,5200.0,NULL,'BAY-PLAST-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(12,5,'RM-TYRE-90-90-12','MRF 90/90-12 Tubeless EV High-Grip Low-Rolling-Resistance Tyre','PCS','4011',28.0,180.0,40.0,500.0,60.0,1350.0,NULL,'BAY-TYRE-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(13,5,'RM-TYRE-110-70-17','Apollo Alpha 110/70-R17 Radial Front Motorcycle Tyre','PCS','4011',28.0,70.0,20.0,200.0,30.0,2600.0,NULL,'BAY-TYRE-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(14,5,'RM-RIM-ALLOY12','12-inch Machined Diamond-Cut Cast Alloy Wheel Rim (Front)','PCS','8714',18.0,95.0,25.0,250.0,35.0,1650.0,NULL,'BAY-RIM-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(15,6,'RM-FORK-TELE12','31mm Front Telescopic Hydraulic Suspension Fork Assembly','SET','8714',18.0,85.0,20.0,200.0,30.0,2450.0,NULL,'BAY-SUSP-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(16,6,'RM-SHOCK-MONO','Rear Gas-Charged Adjustable Mono-Shock Absorber with Coil Spring','PCS','8714',18.0,90.0,25.0,200.0,35.0,1850.0,NULL,'BAY-SUSP-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(17,7,'RM-BRK-DISC-SET','Hydraulic Disc Brake System (220mm Rotor + 2-Piston Caliper + Master Cyl)','SET','8714',18.0,120.0,30.0,300.0,45.0,1950.0,NULL,'BAY-BRK-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(18,7,'RM-BRK-CBS-VALVE','Combi-Braking Proportioning Valve & Braided Steel Hose Set','SET','8714',18.0,140.0,35.0,350.0,50.0,720.0,NULL,'BAY-BRK-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(19,8,'RM-HARN-MAIN','Flame-Retardant Main Wiring Harness with Sealed Delphi Connectors','PCS','8544',18.0,110.0,30.0,300.0,40.0,1800.0,NULL,'BAY-ELEC-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(20,8,'RM-LED-HEAD','Twin Projector All-LED Headlamp with Integrated DRL & Turn Indicators','PCS','8512',18.0,85.0,20.0,200.0,30.0,2100.0,NULL,'BAY-ELEC-03','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(21,9,'RM-TFT-7INCH','7-inch Waterproof IP67 Color TFT Touchscreen Digital Instrument Cluster','PCS','9029',18.0,60.0,15.0,150.0,25.0,4500.0,NULL,'BAY-IOT-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(22,9,'RM-IOT-4G-GPS','4G LTE Telematics Control Unit (TCU) with GPS & Bluetooth Keyless Receiver','PCS','8517',18.0,75.0,20.0,200.0,30.0,2300.0,NULL,'BAY-IOT-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(23,11,'RM-PNT-WHITE-PU','Automotive Grade 2K Polyurethane Paint - Pearl Alpine White (5L Pack)','CAN','3208',18.0,40.0,10.0,100.0,15.0,3400.0,NULL,'BAY-PNT-01','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(24,11,'RM-PNT-CLEAR2K','High Solid Scratch-Resistant 2K Clear Coat (5L Pack + Hardener)','CAN','3208',18.0,35.0,10.0,100.0,15.0,2900.0,NULL,'BAY-PNT-02','2026-10-08 02:29:36');
INSERT INTO mfg_raw_materials VALUES(25,10,'RM-FASTENER-KIT','Grade 8.8 Stainless Steel Hex Socket Flange Fastener & Clip Assortment Kit','SET','7318',18.0,150.0,30.0,400.0,50.0,450.0,NULL,'BAY-FAST-01','2026-10-08 02:29:36');
CREATE TABLE mfg_accessories_master (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                accessory_code TEXT NOT NULL UNIQUE,
                accessory_name TEXT NOT NULL,
                fitment_type TEXT DEFAULT 'UNIVERSAL',
                cost_price REAL DEFAULT 0.00,
                selling_price REAL DEFAULT 0.00,
                current_stock REAL DEFAULT 0.00,
                gst_rate REAL DEFAULT 18.00,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_accessories_master VALUES(1,'ACC-CHG-15A','Ultra-Fast 60V/15A Portable Smart EV Charger with Auto-Cutoff','UNIVERSAL',3800.0,5999.0,45.0,18.0,'2026-10-08 02:29:36');
INSERT INTO mfg_accessories_master VALUES(2,'ACC-GUARD-SS','Stainless Steel All-Round Heavy Duty Body Protection Guard & Footrest','E1-URBAN',1400.0,2699.0,60.0,18.0,'2026-10-08 02:29:36');
INSERT INTO mfg_accessories_master VALUES(3,'ACC-BOX-32L','Quick-Release 32-Litre Waterproof Top Luggage Box with Padded Backrest','UNIVERSAL',1800.0,3200.0,30.0,18.0,'2026-10-08 02:29:36');
INSERT INTO mfg_accessories_master VALUES(4,'ACC-HELM-DOT','Aerodynamic Matte-Finish Full Face DOT-Certified Helmet with Bluetooth Slot','UNIVERSAL',1100.0,1950.0,55.0,18.0,'2026-10-08 02:29:36');
INSERT INTO mfg_accessories_master VALUES(5,'ACC-SEAT-GEL','Breathable Anti-Sweat Touring Gel Comfort Seat Cushion with Waterproof Cover','UNIVERSAL',650.0,1250.0,70.0,18.0,'2026-10-08 02:29:36');
INSERT INTO mfg_accessories_master VALUES(6,'ACC-MAT-3D','Precision Laser-Cut 3D Premium Rubber Floor Mat with Anti-Skid Grippers','E1-URBAN',320.0,699.0,95.0,18.0,'2026-10-08 02:29:36');
CREATE TABLE mfg_departments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                dept_code TEXT NOT NULL UNIQUE,
                dept_name TEXT NOT NULL,
                head_name TEXT,
                total_workstations INTEGER DEFAULT 4,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_departments VALUES(1,'MFG-FAB','Chassis & Frame Fabrication Shop','Er. R. Murugesan',6,'2026-10-08 02:29:36');
INSERT INTO mfg_departments VALUES(2,'MFG-PAINT','Automated Paint & Surface Treatment Bay','Er. P. Sathishkumar',4,'2026-10-08 02:29:36');
INSERT INTO mfg_departments VALUES(3,'MFG-BATT','Battery Pack Integration & BMS Testing Unit','Er. K. Deepa',5,'2026-10-08 02:29:36');
INSERT INTO mfg_departments VALUES(4,'MFG-POWERTRAIN','Motor, Drivetrain & Sub-Assembly Line','Er. V. Anand',6,'2026-10-08 02:29:36');
INSERT INTO mfg_departments VALUES(5,'MFG-FINAL-ASSY','Final Vehicle Conveyor Assembly Line','Er. T. Karthik',8,'2026-10-08 02:29:36');
INSERT INTO mfg_departments VALUES(6,'MFG-QC-PDI','Quality Control, Dyno & Road Testing Dept','Er. S. Balamurugan',4,'2026-10-08 02:29:36');
INSERT INTO mfg_departments VALUES(7,'MFG-DISPATCH','Finished Vehicle Yard & Dispatch Warehouse','M. Velusamy',3,'2026-10-08 02:29:36');
CREATE TABLE mfg_production_stages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stage_code TEXT NOT NULL UNIQUE,
                stage_name TEXT NOT NULL,
                department_id INTEGER NOT NULL,
                seq_order INTEGER DEFAULT 1,
                std_duration_mins INTEGER DEFAULT 120,
                inspection_required INTEGER DEFAULT 1,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_production_stages VALUES(1,'STG-01-FRAME','Frame Jig Alignment & Robotic Welding',1,1,90,1,'2026-10-08 02:29:36');
INSERT INTO mfg_production_stages VALUES(2,'STG-02-PAINT','Cathodic Electrodeposition & 2K Gloss Coating',2,2,120,1,'2026-10-08 02:29:36');
INSERT INTO mfg_production_stages VALUES(3,'STG-03-BATT','Battery Module Cell Balancing & BMS Calibration',3,3,75,1,'2026-10-08 02:29:36');
INSERT INTO mfg_production_stages VALUES(4,'STG-04-DRIVE','Motor Mounting, Swingarm & Suspension Fitment',4,4,60,1,'2026-10-08 02:29:36');
INSERT INTO mfg_production_stages VALUES(5,'STG-05-ASSY','Main Harness, TFT Dashboard & Fairing Fitment',5,5,110,1,'2026-10-08 02:29:36');
INSERT INTO mfg_production_stages VALUES(6,'STG-06-DYNO','Chassis Dynamometer & Regenerative Braking Test',6,6,45,1,'2026-10-08 02:29:36');
INSERT INTO mfg_production_stages VALUES(7,'STG-07-PDI','Final 64-Point Pre-Delivery Inspection (PDI) & VIN Marking',6,7,30,1,'2026-10-08 02:29:36');
CREATE TABLE mfg_workstations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                station_code TEXT NOT NULL UNIQUE,
                station_name TEXT NOT NULL,
                stage_id INTEGER NOT NULL,
                capacity_per_shift INTEGER DEFAULT 10,
                status TEXT DEFAULT 'ACTIVE',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_workstations VALUES(1,'WS-FAB-01','Hydraulic Tube Bending & CNC Mitering Jig',1,15,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(2,'WS-FAB-02','MIG/TIG Robotic Frame Welding Fixture Alpha',1,12,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(3,'WS-PNT-01','Automated Down-Draft Paint Booth & Oven 1',2,10,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(4,'WS-BAT-01','Ultrasonic Cell Tab Welder & Impedance Tester',3,20,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(5,'WS-DRV-01','Torque-Controlled Powertrain Subassembly Bench',4,15,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(6,'WS-ASY-01','Continuous Moving Conveyor Line - Station 1 to 4',5,25,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(7,'WS-DYN-01','Dual-Roller Regenerative Dyno & Headlight Beam Tester',6,30,'ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_workstations VALUES(8,'WS-PDI-01','Precision Fiber-Laser VIN Engraving & Final PDI Bay',7,30,'ACTIVE','2026-10-08 02:29:36');
CREATE TABLE mfg_machinery (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                machine_code TEXT NOT NULL UNIQUE,
                machine_name TEXT NOT NULL,
                department_id INTEGER NOT NULL,
                model_number TEXT,
                purchase_cost REAL DEFAULT 0.00,
                hourly_operating_cost REAL DEFAULT 150.00,
                maintenance_interval_hrs INTEGER DEFAULT 500,
                total_hours_run REAL DEFAULT 0.00,
                status TEXT DEFAULT 'OPERATIONAL',
                last_serviced_date TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_machinery VALUES(1,'MCH-CNC-01','Trumpf CNC 3D Tube Laser Cutting & Bending System',1,'TruLaser Tube 5000',3500000.0,450.0,300,1420.5,'OPERATIONAL',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_machinery VALUES(2,'MCH-ROBOT-01','ABB 6-Axis Robotic Arm Welding Cell with Dual Positioner',1,'IRB 2600ID',2800000.0,380.0,400,1180.0,'OPERATIONAL',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_machinery VALUES(3,'MCH-PAINT-01','Eisenmann Automated Recirculating Thermal Cure Paint Booth',2,'EcoDryScrubber-300',4200000.0,520.0,250,940.0,'OPERATIONAL',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_machinery VALUES(4,'MCH-BATTEST-01','Chroma 17020 Regenerative Battery Pack Charge/Discharge Test Bay',3,'Chroma 17020-60V',1800000.0,220.0,500,2100.0,'OPERATIONAL',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_machinery VALUES(5,'MCH-DYNO-01','Mustang Automated Electric Vehicle Chassis Dynamometer',6,'MD-AWD-500-EV',2400000.0,300.0,350,880.0,'OPERATIONAL',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_machinery VALUES(6,'MCH-LASER-01','Keyence High-Speed Deep Engraving Fiber Laser VIN Marker',6,'MD-X2500',950000.0,120.0,600,620.0,'OPERATIONAL',NULL,'2026-10-08 02:29:36');
CREATE TABLE mfg_employees (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                emp_code TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                department_id INTEGER NOT NULL,
                designation TEXT NOT NULL,
                labour_type TEXT DEFAULT 'PERMANENT',
                daily_wage REAL DEFAULT 800.00,
                hourly_rate REAL DEFAULT 100.00,
                shift TEXT DEFAULT 'GENERAL_DAY',
                mobile TEXT,
                status TEXT DEFAULT 'ACTIVE',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_employees VALUES(1,'EMP-MFG-01','K. Senthilkumar',1,'Senior Welder & Jig Specialist','PERMANENT',950.0,120.0,'DAY_SHIFT_A','9842101122','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(2,'EMP-MFG-02','M. Praveenkumar',1,'Robotics Operator','PERMANENT',900.0,112.5,'DAY_SHIFT_A','9842101123','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(3,'EMP-MFG-03','R. Boopathy',2,'Master Painter & Colorist','PERMANENT',950.0,120.0,'DAY_SHIFT_A','9842101124','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(4,'EMP-MFG-04','G. Manickam',3,'Battery Module Technician','PERMANENT',900.0,112.5,'DAY_SHIFT_A','9842101125','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(5,'EMP-MFG-05','T. Saravanan',4,'Powertrain Line Incharge','PERMANENT',1000.0,125.0,'DAY_SHIFT_A','9842101126','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(6,'EMP-MFG-06','A. Dhanapal',5,'Assembly Line Technician','CONTRACT',750.0,93.75,'DAY_SHIFT_A','9842101127','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(7,'EMP-MFG-07','V. Rajeshwaran',5,'Electrical Harness Technician','PERMANENT',850.0,106.25,'DAY_SHIFT_A','9842101128','ACTIVE','2026-10-08 02:29:36');
INSERT INTO mfg_employees VALUES(8,'EMP-MFG-08','N. Kavitha',6,'Lead Quality Inspection Engineer','PERMANENT',1200.0,150.0,'DAY_SHIFT_A','9842101129','ACTIVE','2026-10-08 02:29:36');
CREATE TABLE mfg_boms (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bom_code TEXT NOT NULL UNIQUE,
                model_id INTEGER NOT NULL,
                variant_id INTEGER,
                revision_no TEXT DEFAULT 'REV-1.0',
                std_labour_cost REAL DEFAULT 0.00,
                std_machine_cost REAL DEFAULT 0.00,
                expected_wastage_pct REAL DEFAULT 2.00,
                production_overhead REAL DEFAULT 0.00,
                total_estimated_cost REAL DEFAULT 0.00,
                status TEXT DEFAULT 'APPROVED',
                approved_by TEXT DEFAULT 'Chief Engineer',
                notes TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_boms VALUES(1,'BOM-E1-STD-V1',1,2,'REV-2.1',2400.0,1800.0,1.5,1500.0,58820.0,'APPROVED','Chief Engineer','Standard Production BOM for Aero E1 with 2.8 kWh LiFePO4 battery and Smart TFT instrument display.','2026-10-08 02:29:36');
INSERT INTO mfg_boms VALUES(2,'BOM-THUNDER-300-V1',3,5,'REV-1.0',3800.0,2600.0,1.199999999999999956,2200.0,94500.0,'APPROVED','Chief Engineer','High-Performance Electric Motorcycle with 5.0 kWh Liquid-Cool Pack and 8.5 kW Mid-Drive PMSM.','2026-10-08 02:29:36');
CREATE TABLE mfg_bom_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bom_id INTEGER NOT NULL,
                item_type TEXT DEFAULT 'RAW_MATERIAL',
                material_id INTEGER NOT NULL,
                required_qty REAL NOT NULL,
                uom TEXT DEFAULT 'PCS',
                scrap_allowance_pct REAL DEFAULT 0.00,
                unit_cost REAL DEFAULT 0.00,
                total_cost REAL DEFAULT 0.00,
                seq_no INTEGER DEFAULT 1
            );
INSERT INTO mfg_bom_items VALUES(1,1,'RAW_MATERIAL',1,1.0,'PCS',0.0,26500.0,26500.0,1);
INSERT INTO mfg_bom_items VALUES(2,1,'RAW_MATERIAL',4,1.0,'PCS',0.0,8500.0,8500.0,2);
INSERT INTO mfg_bom_items VALUES(3,1,'RAW_MATERIAL',6,1.0,'PCS',0.0,5800.0,5800.0,3);
INSERT INTO mfg_bom_items VALUES(4,1,'RAW_MATERIAL',7,1.0,'PCS',0.0,4800.0,4800.0,4);
INSERT INTO mfg_bom_items VALUES(5,1,'RAW_MATERIAL',9,1.0,'PCS',0.0,2400.0,2400.0,5);
INSERT INTO mfg_bom_items VALUES(6,1,'RAW_MATERIAL',10,1.0,'SET',0.0,3900.0,3900.0,6);
INSERT INTO mfg_bom_items VALUES(7,1,'RAW_MATERIAL',12,2.0,'PCS',0.0,1350.0,2700.0,7);
INSERT INTO mfg_bom_items VALUES(8,1,'RAW_MATERIAL',14,1.0,'PCS',0.0,1650.0,1650.0,8);
INSERT INTO mfg_bom_items VALUES(9,1,'RAW_MATERIAL',15,1.0,'SET',0.0,2450.0,2450.0,9);
INSERT INTO mfg_bom_items VALUES(10,1,'RAW_MATERIAL',16,1.0,'PCS',0.0,1850.0,1850.0,10);
INSERT INTO mfg_bom_items VALUES(11,1,'RAW_MATERIAL',17,1.0,'SET',0.0,1950.0,1950.0,11);
INSERT INTO mfg_bom_items VALUES(12,1,'RAW_MATERIAL',19,1.0,'PCS',0.0,1800.0,1800.0,12);
INSERT INTO mfg_bom_items VALUES(13,1,'RAW_MATERIAL',20,1.0,'PCS',0.0,2100.0,2100.0,13);
INSERT INTO mfg_bom_items VALUES(14,1,'RAW_MATERIAL',21,1.0,'PCS',0.0,4500.0,4500.0,14);
INSERT INTO mfg_bom_items VALUES(15,1,'RAW_MATERIAL',25,1.0,'SET',0.0,450.0,450.0,15);
CREATE TABLE mfg_production_plans (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                plan_code TEXT NOT NULL UNIQUE,
                plan_title TEXT NOT NULL,
                plan_type TEXT DEFAULT 'MONTHLY',
                target_date_start TEXT NOT NULL,
                target_date_end TEXT NOT NULL,
                total_target_units INTEGER DEFAULT 50,
                status TEXT DEFAULT 'ACTIVE',
                notes TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_production_plans VALUES(1,'PLAN-2026-OCT','October 2026 Festive Production Blitz (Diwali Demand Surge)','MONTHLY','2026-10-01','2026-10-31',120,'ACTIVE','High-priority plan to fulfill statewide dealership bookings and direct bookings across Tamil Nadu.','2026-10-08 02:29:36');
CREATE TABLE mfg_production_orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                order_code TEXT NOT NULL UNIQUE,
                plan_id INTEGER,
                model_id INTEGER NOT NULL,
                variant_id INTEGER,
                planned_qty INTEGER NOT NULL,
                completed_qty INTEGER DEFAULT 0,
                rejected_qty INTEGER DEFAULT 0,
                priority TEXT DEFAULT 'NORMAL',
                start_date TEXT,
                target_completion_date TEXT,
                actual_completion_date TEXT,
                status TEXT DEFAULT 'PLANNED',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_production_orders VALUES(1,'PO-2026-001',1,1,2,50,42,1,'HIGH','2026-10-01','2026-10-15',NULL,'IN_PROGRESS','2026-10-08 02:29:36');
INSERT INTO mfg_production_orders VALUES(2,'PO-2026-002',1,3,5,25,20,0,'HIGH','2026-10-05','2026-10-20',NULL,'IN_PROGRESS','2026-10-08 02:29:36');
INSERT INTO mfg_production_orders VALUES(3,'PO-2026-003',1,2,3,30,0,0,'NORMAL','2026-10-15','2026-10-30',NULL,'PLANNED','2026-10-08 02:29:36');
INSERT INTO mfg_production_orders VALUES(4,'PO-2026-004',1,4,6,15,15,0,'NORMAL','2026-09-20','2026-10-05',NULL,'COMPLETED','2026-10-08 02:29:36');
CREATE TABLE mfg_production_lots (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                lot_number TEXT NOT NULL UNIQUE,
                order_id INTEGER NOT NULL,
                model_id INTEGER NOT NULL,
                variant_id INTEGER,
                batch_size INTEGER NOT NULL,
                completed_count INTEGER DEFAULT 0,
                rejected_count INTEGER DEFAULT 0,
                total_material_cost REAL DEFAULT 0.00,
                total_labour_cost REAL DEFAULT 0.00,
                total_machine_cost REAL DEFAULT 0.00,
                total_overhead_cost REAL DEFAULT 0.00,
                net_loss_cost REAL DEFAULT 0.00,
                unit_mfg_cost REAL DEFAULT 0.00,
                status TEXT DEFAULT 'IN_PRODUCTION',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_production_lots VALUES(1,'LOT-E1-2026-01',1,1,2,25,25,0,1267500.0,60000.0,45000.0,37500.0,4200.0,56568.0,'COMPLETED','2026-10-08 02:29:36');
INSERT INTO mfg_production_lots VALUES(2,'LOT-E1-2026-02',1,1,2,25,17,1,1267500.0,42000.0,31000.0,25500.0,2800.0,56850.0,'IN_PRODUCTION','2026-10-08 02:29:36');
INSERT INTO mfg_production_lots VALUES(3,'LOT-TH-2026-01',2,3,5,25,20,0,2145000.0,76000.0,52000.0,44000.0,3100.0,92804.0,'IN_PRODUCTION','2026-10-08 02:29:36');
CREATE TABLE mfg_material_issues (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                issue_voucher_no TEXT NOT NULL UNIQUE,
                order_id INTEGER NOT NULL,
                lot_id INTEGER NOT NULL,
                issued_by TEXT DEFAULT 'Storekeeper',
                issue_date TEXT NOT NULL,
                status TEXT DEFAULT 'ISSUED',
                notes TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_material_issues VALUES(1,'MIV-2026-001',1,1,'M. Velusamy (Store Incharge)','2026-10-02','ISSUED','Full batch material issue for 25 units of Aero E1','2026-10-08 02:29:36');
CREATE TABLE mfg_material_issue_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                issue_id INTEGER NOT NULL,
                material_id INTEGER NOT NULL,
                qty_required REAL NOT NULL,
                qty_issued REAL NOT NULL,
                qty_returned REAL DEFAULT 0.00,
                unit_cost REAL DEFAULT 0.00,
                total_cost REAL DEFAULT 0.00
            );
INSERT INTO mfg_material_issue_items VALUES(1,1,1,25.0,25.0,0.0,26500.0,662500.0);
INSERT INTO mfg_material_issue_items VALUES(2,1,4,25.0,25.0,0.0,8500.0,212500.0);
INSERT INTO mfg_material_issue_items VALUES(3,1,6,25.0,25.0,0.0,5800.0,145000.0);
INSERT INTO mfg_material_issue_items VALUES(4,1,7,25.0,25.0,0.0,4800.0,120000.0);
INSERT INTO mfg_material_issue_items VALUES(5,1,10,25.0,25.0,0.0,3900.0,97500.0);
CREATE TABLE mfg_job_cards (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                job_card_code TEXT NOT NULL UNIQUE,
                order_id INTEGER NOT NULL,
                lot_id INTEGER NOT NULL,
                stage_id INTEGER NOT NULL,
                workstation_id INTEGER,
                assigned_employee_id INTEGER,
                machine_id INTEGER,
                planned_qty INTEGER DEFAULT 1,
                completed_qty INTEGER DEFAULT 0,
                rejected_qty INTEGER DEFAULT 0,
                start_time TEXT,
                end_time TEXT,
                duration_mins INTEGER DEFAULT 0,
                status TEXT DEFAULT 'PENDING',
                remarks TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_job_cards VALUES(1,'JC-2026-001',1,1,1,1,1,2,25,25,0,NULL,NULL,90,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(2,'JC-2026-002',1,1,2,3,3,3,25,25,0,NULL,NULL,120,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(3,'JC-2026-003',1,1,3,4,4,4,25,25,0,NULL,NULL,75,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(4,'JC-2026-004',1,1,4,5,5,NULL,25,25,0,NULL,NULL,60,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(5,'JC-2026-005',1,1,5,6,6,NULL,25,25,0,NULL,NULL,110,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(6,'JC-2026-006',1,1,6,7,8,5,25,25,0,NULL,NULL,45,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(7,'JC-2026-007',1,1,7,8,8,6,25,25,0,NULL,NULL,30,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(8,'JC-2026-008',1,2,1,2,2,2,25,25,0,NULL,NULL,90,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(9,'JC-2026-009',1,2,2,3,3,3,25,25,0,NULL,NULL,120,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(10,'JC-2026-010',1,2,3,4,4,4,25,25,0,NULL,NULL,75,'COMPLETED',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(11,'JC-2026-011',1,2,4,5,5,NULL,25,20,0,NULL,NULL,50,'IN_PROGRESS',NULL,'2026-10-08 02:29:36');
INSERT INTO mfg_job_cards VALUES(12,'JC-2026-012',1,2,5,6,7,NULL,25,17,1,NULL,NULL,40,'IN_PROGRESS',NULL,'2026-10-08 02:29:36');
CREATE TABLE mfg_labour_attendance (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                employee_id INTEGER NOT NULL,
                attendance_date TEXT NOT NULL,
                shift TEXT DEFAULT 'GENERAL',
                in_time TEXT DEFAULT '09:00',
                out_time TEXT DEFAULT '18:00',
                total_hours REAL DEFAULT 8.0,
                overtime_hours REAL DEFAULT 0.0,
                idle_hours REAL DEFAULT 0.0,
                daily_cost REAL DEFAULT 800.00,
                status TEXT DEFAULT 'PRESENT',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_labour_attendance VALUES(1,1,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(2,1,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(3,1,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(4,2,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(5,2,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(6,2,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(7,3,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(8,3,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(9,3,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(10,4,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(11,4,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(12,4,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(13,5,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(14,5,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(15,5,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(16,6,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(17,6,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(18,6,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(19,7,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(20,7,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(21,7,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(22,8,'2026-10-06','GENERAL_DAY','08:55','18:15',8.5,0.5,0.2000000000000000111,920.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(23,8,'2026-10-07','GENERAL_DAY','08:50','19:00',9.0,1.0,0.0,1050.0,'PRESENT','2026-10-08 02:29:36');
INSERT INTO mfg_labour_attendance VALUES(24,8,'2026-10-08','GENERAL_DAY','09:00','18:00',8.0,0.0,0.0,850.0,'PRESENT','2026-10-08 02:29:36');
CREATE TABLE mfg_wastage_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                wastage_code TEXT NOT NULL UNIQUE,
                log_date TEXT NOT NULL,
                order_id INTEGER,
                lot_id INTEGER,
                material_id INTEGER NOT NULL,
                quantity REAL NOT NULL,
                unit_cost REAL DEFAULT 0.00,
                total_loss_cost REAL DEFAULT 0.00,
                reason TEXT NOT NULL,
                department_id INTEGER,
                approval_status TEXT DEFAULT 'APPROVED',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_wastage_logs VALUES(1,'WST-2026-001','2026-10-03',1,1,10,1.0,3900.0,3900.0,'ABS cowl surface hairline crack during pneumatic torque fastening',5,'APPROVED','2026-10-08 02:29:36');
INSERT INTO mfg_wastage_logs VALUES(2,'WST-2026-002','2026-10-05',1,2,24,0.5,3400.0,1700.0,'Paint purge line blockage and overspray trim loss',2,'APPROVED','2026-10-08 02:29:36');
INSERT INTO mfg_wastage_logs VALUES(3,'WST-2026-003','2026-10-06',2,3,19,1.0,1800.0,1800.0,'Main harness pin connector damaged during terminal pull test',3,'APPROVED','2026-10-08 02:29:36');
CREATE TABLE mfg_scrap_register (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                scrap_code TEXT NOT NULL UNIQUE,
                log_date TEXT NOT NULL,
                material_id INTEGER,
                scrap_type TEXT DEFAULT 'METAL_TURNINGS',
                quantity REAL NOT NULL,
                recovered_rate REAL DEFAULT 0.00,
                total_recovery_value REAL DEFAULT 0.00,
                buyer_name TEXT,
                status TEXT DEFAULT 'STOCKED',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_scrap_register VALUES(1,'SCRAP-2026-001','2026-10-04',7,'HIGH_TENSILE_STEEL_OFFCUTS',140.0,38.0,5320.0,'Erode Metal Recyclers Pvt Ltd','SOLD','2026-10-08 02:29:36');
INSERT INTO mfg_scrap_register VALUES(2,'SCRAP-2026-002','2026-10-06',9,'ALUMINUM_SWARF_TURNINGS',85.0,145.0,12325.0,'Kongu Alloys Foundry','SOLD','2026-10-08 02:29:36');
INSERT INTO mfg_scrap_register VALUES(3,'SCRAP-2026-003','2026-10-07',10,'ABS_PLASTIC_RUNNERS',45.0,55.0,2475.0,'Green Polymers Tirupur','STOCKED','2026-10-08 02:29:36');
CREATE TABLE mfg_production_losses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                loss_code TEXT NOT NULL UNIQUE,
                log_date TEXT NOT NULL,
                order_id INTEGER,
                lot_id INTEGER,
                loss_type TEXT NOT NULL,
                amount REAL DEFAULT 0.00,
                notes TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_production_losses VALUES(1,'LOSS-2026-001','2026-10-04',1,1,'POWER_OUTAGE_DG_SWITCH_DELAY',1800.0,'TNEB grid trip caused 22 mins line stoppage and reheat delay in paint oven','2026-10-08 02:29:36');
INSERT INTO mfg_production_losses VALUES(2,'LOSS-2026-002','2026-10-06',1,2,'TOOL_RECALIBRATION_OVERHEAD',1200.0,'Robotic welding nozzle tip replacement and optical re-zeroing','2026-10-08 02:29:36');
CREATE TABLE mfg_qc_inspections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                inspection_code TEXT NOT NULL UNIQUE,
                inspection_type TEXT DEFAULT 'FINAL_PDI',
                reference_type TEXT DEFAULT 'LOT',
                reference_id INTEGER,
                inspector_name TEXT DEFAULT 'Quality Lead',
                test_date TEXT NOT NULL,
                result TEXT DEFAULT 'PASS',
                remarks TEXT,
                approved_by TEXT DEFAULT 'Chief Inspector',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_qc_inspections VALUES(1,'QC-2026-001','FRAME_ALIGNMENT_3D','LOT',1,'N. Kavitha (Lead QC)','2026-10-02','PASS','Laser CMM 3D alignment within 0.15mm tolerance.','Er. S. Balamurugan (Chief QC)','2026-10-08 02:29:36');
INSERT INTO mfg_qc_inspections VALUES(2,'QC-2026-002','BATTERY_THERMAL_BMS','LOT',1,'Er. K. Deepa (BMS Lead)','2026-10-03','PASS','Cell delta voltage < 8mV across full 60V string. CAN bus heartbeat stable.','Er. S. Balamurugan (Chief QC)','2026-10-08 02:29:36');
INSERT INTO mfg_qc_inspections VALUES(3,'QC-2026-003','CHASSIS_DYNO_BRAKE','LOT',1,'S. Balamurugan','2026-10-04','PASS','Peak wheel torque 48 Nm verified. Regenerative braking efficiency 16.4%.','Er. S. Balamurugan (Chief QC)','2026-10-08 02:29:36');
INSERT INTO mfg_qc_inspections VALUES(4,'QC-2026-004','FINAL_PDI_64POINT','LOT',1,'N. Kavitha (Lead QC)','2026-10-05','PASS','All 25 vehicles passed 64-point PDI checklist, water shower test, and high-voltage insulation test.','Er. S. Balamurugan (Chief QC)','2026-10-08 02:29:36');
CREATE TABLE mfg_finished_vehicles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                chassis_number TEXT NOT NULL UNIQUE,
                motor_number TEXT NOT NULL UNIQUE,
                battery_serial_number TEXT NOT NULL,
                model_id INTEGER NOT NULL,
                variant_id INTEGER,
                color TEXT DEFAULT 'Pearl White',
                lot_id INTEGER,
                manufacturing_date TEXT NOT NULL,
                manufacturing_cost REAL DEFAULT 0.00,
                base_selling_price REAL DEFAULT 0.00,
                qc_certificate_no TEXT,
                status TEXT DEFAULT 'IN_STOCK',
                warehouse_location TEXT DEFAULT 'WAREHOUSE-ERODE-MAIN',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_finished_vehicles VALUES(1,'MD9ERODE1AER00101','MTR-EA-26-8801','BAT-LP-26-0911',1,2,'Pearl Alpine White',1,'2026-10-05',56568.0,89000.0,'PDI-PASS-00101','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(2,'MD9ERODE1AER00102','MTR-EA-26-8802','BAT-LP-26-0912',1,2,'Matte Stealth Black',1,'2026-10-05',56568.0,89000.0,'PDI-PASS-00102','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(3,'MD9ERODE1AER00103','MTR-EA-26-8803','BAT-LP-26-0913',1,2,'Racing Crimson Red',1,'2026-10-05',56568.0,89000.0,'PDI-PASS-00103','ALLOCATED','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(4,'MD9ERODE1AER00104','MTR-EA-26-8804','BAT-LP-26-0914',1,2,'Metallic Aegean Blue',1,'2026-10-05',56568.0,89000.0,'PDI-PASS-00104','SOLD','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(5,'MD9ERODE1AER00105','MTR-EA-26-8805','BAT-LP-26-0915',1,2,'Pearl Alpine White',1,'2026-10-05',56568.0,89000.0,'PDI-PASS-00105','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(6,'MD9ERODE1AER00106','MTR-EA-26-8806','BAT-LP-26-0916',1,2,'Matte Stealth Black',1,'2026-10-05',56568.0,89000.0,'PDI-PASS-00106','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(7,'MD9ERODE3THN00201','MTR-TH-26-9901','BAT-NM-26-4401',3,5,'Titanium Grey & Neon Orange',1,'2026-10-05',92804.0,155000.0,'PDI-PASS-00201','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(8,'MD9ERODE3THN00202','MTR-TH-26-9902','BAT-NM-26-4402',3,5,'Racing Carbon Black',1,'2026-10-05',92804.0,155000.0,'PDI-PASS-00202','ALLOCATED','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(9,'MD9ERODE3THN00203','MTR-TH-26-9903','BAT-NM-26-4403',3,5,'Racing Carbon Black',1,'2026-10-05',92804.0,155000.0,'PDI-PASS-00203','SOLD','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(10,'MD9ERODE4CRG00301','MTR-CG-26-3301','BAT-CG-26-7701',4,6,'Fleet Pure White',1,'2026-10-05',148000.0,235000.0,'PDI-PASS-00301','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(11,'MD9ERODE4CRG00302','MTR-CG-26-3302','BAT-CG-26-7702',4,6,'Fleet Pure White',1,'2026-10-05',148000.0,235000.0,'PDI-PASS-00302','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
INSERT INTO mfg_finished_vehicles VALUES(12,'MD9ERODE5FLC00401','MTR-FL-26-1101','BAT-FL-26-5501',5,8,'Midnight Liquid Chrome',1,'2026-10-05',124500.0,195000.0,'PDI-PASS-00401','IN_STOCK','WAREHOUSE-ERODE-MAIN','2026-10-08 02:29:36');
CREATE TABLE mfg_purchase_orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                po_code TEXT NOT NULL UNIQUE,
                supplier_id INTEGER NOT NULL,
                order_date TEXT NOT NULL,
                expected_date TEXT,
                subtotal REAL DEFAULT 0.00,
                gst_amount REAL DEFAULT 0.00,
                freight_charges REAL DEFAULT 0.00,
                loading_charges REAL DEFAULT 0.00,
                landed_total REAL DEFAULT 0.00,
                status TEXT DEFAULT 'APPROVED',
                notes TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO mfg_purchase_orders VALUES(1,'PO-RM-2026-001',1,'2026-09-25','2026-10-01',530000.0,95400.0,4500.0,1200.0,631100.0,'COMPLETED','Bulk order for 20 units of 2.8 kWh LiFePO4 battery packs for Aero E1 production run.','2026-10-08 02:29:36');
CREATE TABLE mfg_purchase_order_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                po_id INTEGER NOT NULL,
                material_id INTEGER NOT NULL,
                qty_ordered REAL NOT NULL,
                qty_received REAL DEFAULT 0.00,
                unit_rate REAL NOT NULL,
                gst_rate REAL DEFAULT 18.00,
                line_total REAL NOT NULL
            );
INSERT INTO mfg_purchase_order_items VALUES(1,1,1,20.0,20.0,26500.0,18.0,530000.0);
CREATE TABLE mfg_grn (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                grn_code TEXT NOT NULL UNIQUE,
                po_id INTEGER,
                supplier_id INTEGER NOT NULL,
                supplier_invoice_no TEXT,
                invoice_date TEXT,
                total_amount REAL DEFAULT 0.00,
                landed_cost REAL DEFAULT 0.00,
                received_by TEXT DEFAULT 'Store Manager',
                status TEXT DEFAULT 'VERIFIED',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
CREATE TABLE mfg_sales_orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                order_code TEXT NOT NULL UNIQUE,
                customer_id INTEGER NOT NULL,
                vehicle_id INTEGER,
                chassis_number TEXT,
                base_price REAL DEFAULT 0.00,
                accessories_total REAL DEFAULT 0.00,
                gst_amount REAL DEFAULT 0.00,
                discount REAL DEFAULT 0.00,
                net_total REAL DEFAULT 0.00,
                booking_advance REAL DEFAULT 0.00,
                balance_due REAL DEFAULT 0.00,
                delivery_status TEXT DEFAULT 'DELIVERED',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
CREATE TABLE mfg_accessories_sales (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bill_code TEXT NOT NULL UNIQUE,
                customer_id INTEGER,
                customer_name TEXT NOT NULL,
                accessory_id INTEGER NOT NULL,
                quantity REAL DEFAULT 1.0,
                unit_price REAL DEFAULT 0.00,
                gst_amount REAL DEFAULT 0.00,
                grand_total REAL DEFAULT 0.00,
                payment_mode TEXT DEFAULT 'UPI_QR',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
CREATE TABLE mfg_warranty_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                chassis_number TEXT NOT NULL UNIQUE,
                customer_id INTEGER,
                warranty_start_date TEXT NOT NULL,
                warranty_end_date TEXT NOT NULL,
                battery_warranty_years INTEGER DEFAULT 3,
                motor_warranty_years INTEGER DEFAULT 3,
                amc_status TEXT DEFAULT 'ACTIVE',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
CREATE TABLE mfg_machine_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                machine_id INTEGER NOT NULL,
                log_date TEXT NOT NULL,
                run_hours REAL DEFAULT 8.0,
                electricity_units REAL DEFAULT 45.0,
                maintenance_cost REAL DEFAULT 0.00,
                breakdown_hours REAL DEFAULT 0.0,
                operator_name TEXT DEFAULT 'Senior Machinist',
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
CREATE TABLE mfg_accounting_journals (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                voucher_number TEXT NOT NULL UNIQUE,
                voucher_date TEXT NOT NULL,
                voucher_type TEXT NOT NULL,
                debit_account TEXT NOT NULL,
                credit_account TEXT NOT NULL,
                amount REAL NOT NULL,
                narration TEXT,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
INSERT INTO sqlite_sequence VALUES('branches',3);
INSERT INTO sqlite_sequence VALUES('roles',10);
INSERT INTO sqlite_sequence VALUES('permissions',18);
INSERT INTO sqlite_sequence VALUES('role_permissions',18);
INSERT INTO sqlite_sequence VALUES('users',12);
INSERT INTO sqlite_sequence VALUES('vehicle_brands',10);
INSERT INTO sqlite_sequence VALUES('vehicle_models',15);
INSERT INTO sqlite_sequence VALUES('vehicle_inventory',6);
INSERT INTO sqlite_sequence VALUES('part_categories',10);
INSERT INTO sqlite_sequence VALUES('parts',12);
INSERT INTO sqlite_sequence VALUES('customers',6);
INSERT INTO sqlite_sequence VALUES('customer_vehicles',7);
INSERT INTO sqlite_sequence VALUES('leads',3);
INSERT INTO sqlite_sequence VALUES('service_bookings',4);
INSERT INTO sqlite_sequence VALUES('job_cards',6);
INSERT INTO sqlite_sequence VALUES('job_card_complaints',7);
INSERT INTO sqlite_sequence VALUES('job_card_parts',5);
INSERT INTO sqlite_sequence VALUES('job_card_labour',4);
INSERT INTO sqlite_sequence VALUES('suppliers',5);
INSERT INTO sqlite_sequence VALUES('invoices',2);
INSERT INTO sqlite_sequence VALUES('accounts',16);
INSERT INTO sqlite_sequence VALUES('expenses',3);
INSERT INTO sqlite_sequence VALUES('notifications',5);
INSERT INTO sqlite_sequence VALUES('service_reminders',4);
INSERT INTO sqlite_sequence VALUES('login_history',30);
INSERT INTO sqlite_sequence VALUES('whatsapp_logs',2);
INSERT INTO sqlite_sequence VALUES('mfg_vehicle_categories',5);
INSERT INTO sqlite_sequence VALUES('mfg_vehicle_models',5);
INSERT INTO sqlite_sequence VALUES('mfg_vehicle_variants',8);
INSERT INTO sqlite_sequence VALUES('mfg_raw_material_categories',11);
INSERT INTO sqlite_sequence VALUES('mfg_raw_materials',25);
INSERT INTO sqlite_sequence VALUES('mfg_accessories_master',6);
INSERT INTO sqlite_sequence VALUES('mfg_departments',7);
INSERT INTO sqlite_sequence VALUES('mfg_production_stages',7);
INSERT INTO sqlite_sequence VALUES('mfg_workstations',8);
INSERT INTO sqlite_sequence VALUES('mfg_machinery',6);
INSERT INTO sqlite_sequence VALUES('mfg_employees',8);
INSERT INTO sqlite_sequence VALUES('mfg_boms',2);
INSERT INTO sqlite_sequence VALUES('mfg_bom_items',15);
INSERT INTO sqlite_sequence VALUES('mfg_production_plans',1);
INSERT INTO sqlite_sequence VALUES('mfg_production_orders',4);
INSERT INTO sqlite_sequence VALUES('mfg_production_lots',3);
INSERT INTO sqlite_sequence VALUES('mfg_material_issues',1);
INSERT INTO sqlite_sequence VALUES('mfg_material_issue_items',5);
INSERT INTO sqlite_sequence VALUES('mfg_job_cards',12);
INSERT INTO sqlite_sequence VALUES('mfg_labour_attendance',24);
INSERT INTO sqlite_sequence VALUES('mfg_wastage_logs',3);
INSERT INTO sqlite_sequence VALUES('mfg_scrap_register',3);
INSERT INTO sqlite_sequence VALUES('mfg_production_losses',2);
INSERT INTO sqlite_sequence VALUES('mfg_qc_inspections',4);
INSERT INTO sqlite_sequence VALUES('mfg_finished_vehicles',12);
INSERT INTO sqlite_sequence VALUES('mfg_purchase_orders',1);
INSERT INTO sqlite_sequence VALUES('mfg_purchase_order_items',1);
INSERT INTO sqlite_sequence VALUES('vehicle_inspections',1);
INSERT INTO sqlite_sequence VALUES('job_status_history',2);
COMMIT;