-- TMS Database Export
-- Generated on: 2026-05-20 11:14:25
-- Mode: both

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
    id INTEGER,
    name TEXT NOT NULL,
    parent_id INT,
    nature TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    code TEXT,
    PRIMARY KEY (id)
);

INSERT INTO `groups` VALUES ('1', 'Assets', NULL, 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('2', 'Liabilities', NULL, 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('3', 'Income', NULL, 'Income', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('4', 'Expenses', NULL, 'Expenses', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('5', 'Current Assets', '1', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('6', 'Fixed Assets', '1', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('7', 'Investments', '1', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('8', 'Bank Accounts', '5', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('9', 'Cash-in-hand', '5', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('10', 'Sundry Debtors', '5', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('11', 'Stock-in-hand', '5', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('12', 'Loans & Advances (Asset)', '5', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('13', 'TDS Receivable', '5', 'Assets', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('14', 'Capital Account', '2', 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('15', 'Current Liabilities', '2', 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('16', 'Loans (Liability)', '2', 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('17', 'Sundry Creditors', '15', 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('18', 'Duties & Taxes', '15', 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('19', 'Provisions', '15', 'Liabilities', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('20', 'Direct Expenses', '4', 'Expenses', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('21', 'Indirect Expenses', '4', 'Expenses', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('22', 'Direct Incomes', '3', 'Income', '2026-01-06 15:53:20', NULL);
INSERT INTO `groups` VALUES ('23', 'Indirect Incomes', '3', 'Income', '2026-01-06 15:53:20', 'Exe001');

DROP TABLE IF EXISTS `ledgers`;
CREATE TABLE `ledgers` (
    id INTEGER,
    name TEXT NOT NULL,
    group_id INT NOT NULL,
    opening_balance VARCHAR(255) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    code TEXT,
    gst_rate DECIMAL(5,2) DEFAULT 0.00,
    opening_balance_type VARCHAR(10) DEFAULT 'Dr',
    branch_id INTEGER DEFAULT 1,
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(20),
    pan VARCHAR(20),
    gstin VARCHAR(20),
    gst_registration_type VARCHAR(50),
    tds_applicable INTEGER DEFAULT 0,
    tds_section VARCHAR(50),
    tds_rate DECIMAL(5,2) DEFAULT 0.00,
    bank_name VARCHAR(255),
    account_no VARCHAR(50),
    ifsc_code VARCHAR(20),
    credit_limit DECIMAL(15,2) DEFAULT 0.00,
    credit_days INTEGER DEFAULT 0,
    notes TEXT,
    interest_rate DECIMAL(5,2) DEFAULT 0.00,
    loan_tenure_months INTEGER DEFAULT 0,
    loan_start_date DATE DEFAULT NULL,
    depreciation_rate DECIMAL(5,2) DEFAULT 0.00,
    depreciation_days INTEGER DEFAULT 0,
    PRIMARY KEY (id)
);

INSERT INTO `ledgers` VALUES ('1', 'Dummy Vendor', '10', '0', '2026-02-24 17:54:16', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('2', 'Cash in Hand - Manesar', '9', '0', '2026-02-26 16:04:14', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('3', 'Mumbai Client', '10', '0.0', '2026-02-26 17:00:21', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, 'Mumbai', NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('4', 'Delhi Client', '10', '0.0', '2026-02-26 17:00:21', NULL, '0', 'Dr', '3', NULL, NULL, NULL, NULL, 'Delhi', NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('5', 'MAMUJI OVERSEAS LLP', '10', '52000', '2026-02-28 06:45:29', NULL, '0', 'Cr', '1', 'MUFFI BHARMAL', '', '9586800052', 'C/O BUDGET CFS TERMINALS PVT LTD EWC CPS DRONAGIRI NODAL INNSA KARAL NAVI MUMBAI', 'MUMBAI', 'Maharashtra', '400707', 'ABZFM2435P', '24ABZFM2435P1ZH', 'Regular', '0', '', '0', '', '', '', '0', '0', 'FTL CUSTOMER', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('6', 'GUGARWAL GOODS CARRIER', '17', '50000', '2026-02-28 07:05:46', NULL, '0', 'Cr', '1', 'Shailendra Kumar', '', '+91 80106 46893', 'Sahibabad Ghaziabad', 'Ghaziabad', 'Uttar Pradesh', '201005', 'CLQPK8643C', '', 'Regular', '0', '', '0', 'HDFC BANK', '50200064884607', 'HDFC0001266', '0', '0', 'Vender partner', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('7', 'STATE BANK OF INDIA - 4755', '8', '0', '2026-02-28 07:17:59', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('9', 'MASH & COMPANY', '10', '0', '2026-03-09 08:54:33', NULL, '0', 'Cr', '1', 'mMR.SHAQIB KHAN', 'info@mashgroup.in', '8887664072', '87/73-B HINDUSTAN TANNERY COMPOUND , JAJMAU KANPUR-208011', 'KANPUR', 'Uttar Pradesh', '208011', 'AABFM4240M', '09AABFM4240M1Z6', 'Regular', '1', '', '0', 'STATE BANK OF INDIA', '', '', '500000', '90', 'SAFETY SHOES PRODUCT', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('10', 'SHASVAT ROADWAYS', '17', '0', '2026-03-09 08:57:22', NULL, '0', 'Cr', '1', 'PRAVEEN KUMAR', 'moveup19kanpur@gmail.com', '7860079786', 'Benefication Plant(BHQ) 197,198,199,227,228, Forest Comptt, Surjagarh Wooria HIll,Tahsil,EttapalliDist - Gadchiroli,Chandrapur - 442401', 'KANPUR', 'Uttar Pradesh', '208011', '', '', 'Regular', '1', '', '0', '', '', '', '0', '0', 'ALL VEHICLE PROVIDERS', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('12', 'V.K.ROADWAYS', '17', '10000', '2026-03-13 06:45:25', NULL, '0', 'Cr', '1', 'SUDHIR MISHRA', '', '9151612584', 'Patel nagar kanpur', 'Kanpur', 'Uttar Pradesh', '208010', '', '', 'Regular', '0', '', '0', '', '', '', '0', '0', 'Vehicle provider for ncr', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('13', 'SUPER WHEELS UTHORISED DEALERS BAJAJ AUTO LTD', '10', '0', '2026-03-18 16:32:40', NULL, '0', 'Dr', '1', 'PRADEEP SINGH', 'moveup19kanpur@gmail.com', '', '125/5 U BLOCK GOVIND NAGAR CTI CHAURAHA KANPUR NAGAR', 'KANPUR', 'Uttar Pradesh', '208006', '', '', 'Regular', '0', '', '0', '', '', '', '0', '0', '', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('14', 'Global sport', '10', '0', '2026-04-09 05:34:06', NULL, '0', 'Dr', '1', '', '', '', '', '', '', '', '', '', 'Regular', '0', '', '0', '', '', '', '0', '0', '', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('15', 'Cash in Hand - FARIDABAD ', '9', '0', '2026-04-09 05:55:53', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('16', 'Cash in Hand - HAZARIBAG', '9', '0', '2026-04-09 16:51:41', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('17', 'Cash in Hand - HAZARIBAGH', '9', '0', '2026-04-09 16:55:06', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('18', 'SOURCE INDIA SHOES', '10', '0', '2026-04-09 16:58:22', NULL, '0', 'Dr', '1', 'SOURCE INDIA SHOES', 'info.gs30@gmail.com', '9990848912', 'Head Office-124/B/119 Govind Nagar,, Kanpur(U.P) Pin-208006', 'KANPUR', 'Uttar Pradesh', '208006', 'ALNPM6501A', '09ALNPM6501A1ZB', 'Regular', '0', '', '0', 'Central Bank of India', '3919303434', 'CBIN0281432', '0', '30', 'SAFETY SHOES', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('19', 'MASH & COMPANY', '10', '0', '2026-04-19 14:16:03', NULL, '0', 'Dr', '1', 'MR. SHAQIB KHAN', 'info@mashgroup.in', '8887664072', '87/73-B HINDUSTAN TANNERY COMPOUND , JAJMAU KANPUR-208011', 'KANPUR', 'Uttar Pradesh', '208011', 'AABFM4240M', '09AABFM4240M1Z6', 'Regular', '1', '', '0', 'STATE BANK OF INDIA', '', '', '0', '0', 'TBB CUSTOMER', '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('20', 'Freight Paid', '20', '0', '2026-04-19 15:32:06', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('21', 'Cash Account', '5', '0.0', '2026-04-19 15:41:36', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');
INSERT INTO `ledgers` VALUES ('22', 'TDS Payable', '18', '0.0', '2026-04-19 15:41:36', NULL, '0', 'Dr', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', NULL, NULL, NULL, '0', '0', NULL, '0', '0', NULL, '0', '0');

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
    id INTEGER,
    name TEXT NOT NULL,
    address TEXT,
    phone TEXT,
    ledger_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    gst TEXT,
    pan TEXT,
    email TEXT,
    state TEXT,
    state_code TEXT,
    pincode TEXT,
    code TEXT,
    default_payment_type VARCHAR(255) DEFAULT 'paid',
    gstin VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (id)
);

INSERT INTO `customers` VALUES ('1', 'Sample Customer 1', 'Address 1', '1234567890', '101', '2026-02-28 16:42:57', 'GSTIN1', NULL, 'customer1@example.com', NULL, NULL, NULL, NULL, 'paid', NULL);
INSERT INTO `customers` VALUES ('5', 'Sample Customer 5', 'Address 5', '0987654321', '1', '2026-02-28 16:42:57', 'GSTIN5', NULL, 'customer5@example.com', NULL, NULL, NULL, NULL, 'paid', NULL);
INSERT INTO `customers` VALUES ('6', 'MAMUJI OVERSEAS LLP', 'Delhi', '', '1', '2026-02-28 19:19:17', NULL, '', '', 'Delhi', NULL, '', NULL, 'paid', '');

DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors` (
    id INTEGER,
    name TEXT NOT NULL,
    address TEXT,
    phone TEXT,
    ledger_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    gst TEXT,
    pan TEXT,
    email TEXT,
    code TEXT,
    state VARCHAR(255),
    state_code VARCHAR(255),
    tds_section VARCHAR(255),
    tds_rate VARCHAR(255) DEFAULT 0,
    PRIMARY KEY (id)
);

INSERT INTO `vendors` VALUES ('1', 'ABC Transport Services', '123 Main Street, Mumbai, Maharashtra 400001', '022-12345678', '0', '2026-04-19 15:17:26', '27AABCU9603R1ZX', 'AABCU9603R', 'contact@abctransport.com', 'ABC001', 'Maharashtra', '27', '194C', '2.0');
INSERT INTO `vendors` VALUES ('2', 'XYZ Logistics Pvt Ltd', '456 Business Park, Delhi, Delhi 110001', '011-23456789', '0', '2026-04-19 15:17:26', '07AAACX1234R1ZV', 'AAACX1234R', 'info@xyzlogistics.com', 'XYZ002', 'Delhi', '07', '194C', '2.0');
INSERT INTO `vendors` VALUES ('3', 'Speed Cargo Movers', '789 Industrial Area, Chennai, Tamil Nadu 600001', '044-34567890', '0', '2026-04-19 15:17:26', '33AAECS5678R1ZT', 'AAECS5678R', 'admin@speedcargo.com', 'SCM003', 'Tamil Nadu', '33', '194C', '2.0');
INSERT INTO `vendors` VALUES ('4', 'Reliable Transport Co', '321 Transport Nagar, Kolkata, West Bengal 700001', '033-45678901', '0', '2026-04-19 15:17:26', '19AAECR9012R1ZU', 'AAECR9012R', 'operations@reliabletransport.com', 'RTC004', 'West Bengal', '19', '194C', '2.0');
INSERT INTO `vendors` VALUES ('5', 'Golden Logistics Solutions', '654 Corporate Hub, Bangalore, Karnataka 560001', '080-56789012', '0', '2026-04-19 15:17:26', '29AAECG3456R1ZW', 'AAECG3456R', 'support@goldenlogistics.com', 'GLS005', 'Karnataka', '29', '194C', '2.0');

DROP TABLE IF EXISTS `banks`;
CREATE TABLE `banks` (
    id INTEGER,
    bank_name TEXT NOT NULL,
    account_number TEXT,
    ifsc_code TEXT,
    ledger_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    code TEXT,
    PRIMARY KEY (id)
);

INSERT INTO `banks` VALUES ('1', 'STATE BANK OF INDIA', '42540674755', 'SBIN0020814', '7', '2026-02-28 07:17:59', NULL);

DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` (
    id INTEGER,
    name TEXT NOT NULL,
    code TEXT,
    address TEXT,
    type TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `branches` VALUES ('1', 'KANPUR', 'CNB', 'KANPUR', 'Branch', '2026-01-06 16:13:09');
INSERT INTO `branches` VALUES ('2', 'Manesar', 'MNSR', '', 'Branch', '2026-02-26 16:04:14');
INSERT INTO `branches` VALUES ('3', 'Delhi', 'DEL', 'Delhi', 'Branch', '2026-02-26 17:00:21');
INSERT INTO `branches` VALUES ('4', 'FARIDABAD ', 'FBD', 'Faridabad ', 'Branch', '2026-04-09 05:55:53');
INSERT INTO `branches` VALUES ('5', 'HAZARIBAGH', 'HZB', 'HAZARIBAGH', 'Branch', '2026-04-09 16:51:41');

DROP TABLE IF EXISTS `consignees`;
CREATE TABLE `consignees` (
    id INTEGER,
    customer_id INT NOT NULL,
    consignee_name TEXT NOT NULL,
    address TEXT NOT NULL,
    gst TEXT,
    pan TEXT,
    phone TEXT,
    email TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    code TEXT,
    city_id INT,
    PRIMARY KEY (id)
);

INSERT INTO `consignees` VALUES ('1', '4', 'OM LOGISTICS LTD', '730
eros corporate park', '24KULPS0110J1ZJ', 'BTIPM5158F', '07695065498', 'kamalkmishra8@gmail.com', '2026-02-26 18:13:41', NULL, '');
INSERT INTO `consignees` VALUES ('2', '5', 'MAMUJI OVERSEAS LLP', 'C/O BUDGET CFS TERMINALS PVT LTD EWC CPS DRONAGIRI NODAL INNSA KARAL NAVI MUMBAI', '24ABZFM2435P1ZH', 'ABZFM2435P', '9586800052', '', '2026-02-28 06:45:29', NULL, '801');
INSERT INTO `consignees` VALUES ('3', '5', 'Mamiji overseas llp', 'C/o Budget terminal pvt ltd EWC CPS DRONAGIRI NODAL INNSA KARAL NAVI MUMBAI ', '24ABZFM2435P1ZH', 'ABZFM2435P', '95868 00052', 'moveup19kanpur@gmail.com', '2026-02-28 07:01:05', NULL, '801');
INSERT INTO `consignees` VALUES ('4', '9', 'NORTHERN COAL FIELDS LIMITED ( M.P.)', 'CENTRAL STORES , JAYANT COLLIERY P.O.JAYANT DIST. SINGRAULI M.P. SIDHI MADHYA PRADESH -486890 ', '23AABCN4884H1ZE', '', '9425925820', 'info@mashgroup.in', '2026-03-09 09:04:10', NULL, '312');
INSERT INTO `consignees` VALUES ('5', '11', 'SOURCE INDIA SHOES ', 'B2 BS COMPLEX NAYA BANS SECTOR -15 NOIDA ', '09ANLPM6501A1ZB', 'ANLPM6501A', '09990848912', 'info.gs30@gmail.com', '2026-03-13 06:41:16', NULL, '638');
INSERT INTO `consignees` VALUES ('6', '13', 'SUPER WHEELS AUTOMOBILES PVT LTD ', 'TINDWARI ROAD NEAR SALES TAX OFFICE BANDA UTTAR PRADESH -210001', '09ABBCS3457N1ZT', 'ABBCS3457N', '7800843866', 'moveup19kanpur@gmail.com', '2026-03-18 16:38:19', NULL, '803');
INSERT INTO `consignees` VALUES ('7', '11', 'JAKWINS PRODUCT & MARKETING ', 'PKB 54 A SECTOR 122 BEHIND CNG PUMP PARTHLA CHOUK NOIDA UP-201301', NULL, NULL, '8178220116', NULL, '2026-03-19 09:34:12', NULL, NULL);
INSERT INTO `consignees` VALUES ('8', '13', 'SUPER WHEELS AUTOMOBILE PVT LTD ', 'BEHIND BLUE WORLD WATERPARK MANDHANA
KANPUR -209217', '09ABBCS3457N1ZT', '', '+917800843866', 'moveup19kanpur@gmail.com', '2026-03-19 16:25:54', NULL, '802');
INSERT INTO `consignees` VALUES ('9', '11', 'Ramp 360 ground handling service pvt ltd ', 'GH 1 & 2 ground floor passenger Terminal building Manohar international airport mopa north', '30AAMCT1846N1Z1', 'AAMCT1846N1Z', '9999776417', 'moveup19kanpur@gmail.com', '2026-03-26 06:23:05', NULL, '804');
INSERT INTO `consignees` VALUES ('10', '1', 'Global sport', '2877/1,sec-1 ground floor and first 23*24 floor driving road near lakhani chowk Faridabad ', '06AMEPB7424K1Z6', '', '9891766706', '', '2026-04-09 05:34:06', NULL, '176');
INSERT INTO `consignees` VALUES ('11', '11', 'THRIVENI SAINIK MINING PRIVATE LIMITED ', 'NTPC Pakri Barwadih Mines,(6001 - Mines) C/o NTPC Limited,Langatu P.O: Barkagaon,Hazaribag Hazaribag-825311,Jharkhand,India', '20AAFCT6119D2ZT', 'AAFCT6119D', '7633998742', 'info.gs30@gmail.com', '2026-04-09 16:54:53', NULL, '232');
INSERT INTO `consignees` VALUES ('12', '9', 'ORDNANCE FACTORY PROJECT KORWA', 'JUNIOR WORK MANAGER ORDNANCE FACTORY PROJECT KORWA AMETHI SULTANPUR - 227412', '09AAVCA6457D1Z8', 'AAVCA6457D', '9453729215', 'info@mashgroup.in', '2026-04-19 14:21:49', NULL, '806');
INSERT INTO `consignees` VALUES ('13', '18', 'OIL LUBE SYSTEM PVT LTD ', 'BADKHAL PALI ROAD INDUSTRIAL AREA VILLAGE BHANKRI FARIDABAD ', '06AABCO7087R2ZM', 'AABCO7087R', '9555896250', 'info.gs30@gmail.com', '2026-05-20 05:03:51', NULL, '176');

DROP TABLE IF EXISTS `expense_types`;
CREATE TABLE `expense_types` (
    id INTEGER,
    expense_type TEXT NOT NULL,
    narration TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    group_id INT,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `dockets`;
CREATE TABLE `dockets` (
    id INTEGER,
    docket_no TEXT NOT NULL,
    date DATETIME NOT NULL,
    payment_type TEXT NOT NULL,
    origin TEXT NOT NULL,
    destination TEXT NOT NULL,
    customer_id INT NOT NULL,
    consignee_id INT NOT NULL,
    vendor_id INT NOT NULL,
    vehicle_type TEXT,
    vehicle_no TEXT,
    driver_name TEXT,
    driver_no TEXT,
    contract_amount VARCHAR(255),
    goods_value VARCHAR(255),
    inv_date DATETIME,
    ewaybill_no TEXT,
    basic_charges VARCHAR(255) DEFAULT 0.00,
    docket_charges VARCHAR(255) DEFAULT 0.00,
    fuel_charges VARCHAR(255) DEFAULT 0.00,
    pickup_delivery_charges VARCHAR(255) DEFAULT 0.00,
    oda_charges VARCHAR(255) DEFAULT 0.00,
    hamali_charges VARCHAR(255) DEFAULT 0.00,
    detention_charges VARCHAR(255) DEFAULT 0.00,
    subtotal VARCHAR(255) DEFAULT 0.00,
    gst_rate VARCHAR(255) DEFAULT 18.00,
    gst_amount VARCHAR(255) DEFAULT 0.00,
    total VARCHAR(255) DEFAULT 0.00,
    description TEXT,
    status TEXT,
    billing_status TEXT,
    payment_status TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    consignor_id INT,
    no_of_packages INT DEFAULT 0,
    actual_weight VARCHAR(255) DEFAULT 0.00,
    charged_weight VARCHAR(255) DEFAULT 0.00,
    sac_hsn VARCHAR(255) DEFAULT '9967',
    invoice_no TEXT,
    ewaybill_expiry DATETIME,
    epod_image TEXT,
    hardcopy_date DATETIME,
    receiver_name TEXT,
    receiver_phone TEXT,
    pod_image TEXT,
    vendor_loading_charges VARCHAR(255) DEFAULT 0,
    vendor_unloading_charges VARCHAR(255) DEFAULT 0,
    vendor_loading_detention VARCHAR(255) DEFAULT 0,
    vendor_unloading_detention VARCHAR(255) DEFAULT 0,
    vendor_other_charges VARCHAR(255) DEFAULT 0,
    vendor_mamul_charges VARCHAR(255) DEFAULT 0,
    vendor_late_penalty VARCHAR(255) DEFAULT 0,
    vendor_bill_status VARCHAR(255) DEFAULT 'Pending',
    branch_id INTEGER DEFAULT 1,
    booking_mode VARCHAR(20) DEFAULT 'FTL',
    trip_no VARCHAR(50) DEFAULT NULL,
    trip_date DATE DEFAULT NULL,
    fuel_liters DECIMAL(10,2) DEFAULT 0,
    urea_liters DECIMAL(10,2) DEFAULT 0,
    driver_expenses DECIMAL(10,2) DEFAULT 0,
    advance_amount DECIMAL(10,2) DEFAULT 0,
    paid_fuel DECIMAL(10,2) DEFAULT 0,
    paid_expenses DECIMAL(10,2) DEFAULT 0,
    paid_fuel_liters DECIMAL(10,2) DEFAULT 0,
    paid_urea_liters DECIMAL(10,2) DEFAULT 0,
    fuel_rate DECIMAL(10,2) DEFAULT 0,
    urea_rate DECIMAL(10,2) DEFAULT 0,
    paid_urea DECIMAL(10,2) DEFAULT 0,
    created_by INTEGER DEFAULT 1,
    opening_km INTEGER DEFAULT 0,
    closing_km INTEGER DEFAULT 0,
    ewaybill_date DATETIME DEFAULT NULL,
    ewaybill_status VARCHAR(50) DEFAULT NULL,
    origin_type VARCHAR(20) DEFAULT 'City',
    destination_type VARCHAR(20) DEFAULT 'City',
    origin_city VARCHAR(100),
    origin_branch VARCHAR(100),
    destination_city VARCHAR(100),
    destination_branch VARCHAR(100),
    PRIMARY KEY (id)
);

INSERT INTO `dockets` VALUES ('1', 'DKT-1771955656373', '2026-02-21', 'To Pay', 'Mumbai', 'Pune', '1', '1', '1', NULL, 'MH12AB1004-438', 'Mahesh 36', NULL, NULL, NULL, NULL, NULL, '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '0.0', '18.0', '0.0', '0.0', NULL, 'Active', NULL, NULL, '2026-02-24 17:54:16', '1', '0', '0.0', '0.0', '9967', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', NULL, NULL, '175', '13', '1247', '0', '0', '0', '0', '0', '0', '0', '0', '1', '22591', '23160', NULL, NULL, 'City', 'City', NULL, NULL, NULL, NULL);
INSERT INTO `dockets` VALUES ('6', '997', '2026-02-17', 'topay', 'Delhi', 'Navi Mumbai', '5', '2', '6', '32 ft SXl container ', 'MH46BF6614', 'Deepak', '+918397802623', '50000', '977917', '2026-02-17', '431687930412', '52000', '0', '0', '0', '0', '0', '0', '52000', '0', '0', '52000', 'Safety shoes ', NULL, 'billed', 'pending', '2026-02-28 07:09:41', '2', '465', '5000', '5000', '9967', '1517', '2026-02-21', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', '997', '2026-02-17', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Delhi', '', 'Navi Mumbai', '');
INSERT INTO `dockets` VALUES ('7', '1000', '2026-03-09', 'tbb', 'Kanpur', 'Singrauli', '9', '4', '10', 'BOLERO PICKUP', 'UP77AT5631', 'GURU RAMAYANA', '8922924411', '13000', '504075', '2026-03-09', '441696243643', '18000', '0', '0', '0', '0', '0', '0', '18000.00', '0', '0.00', '18000.00', 'TERM OF DELIVERY : - AGAINST INVOICE NO. 786/ML/278 ON DATE 25-02-2026
PRODUCT : - SAFETY SHOES ', NULL, NULL, 'pending', '2026-03-09 10:17:37', '3', '71', '950', '950', '9967', 'CHALLAN NO.  - 22 ', '2026-03-12', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', '1000', '2026-03-09', '0', '0', '0', '12000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Kanpur', '', 'Singrauli', '');
INSERT INTO `dockets` VALUES ('8', 'DKT/CNB/01001', '2026-03-13', 'tbb', 'Kanpur', 'Noida', '11', '5', '10', '14 ft vehicle ', 'DL01LAE9272', 'SADANAND YADAV', '9717026352', '7000', '448875', '2026-03-12', '491697876597', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'LEATHER SAFETY SHOES SAFARI PRO  ART 1104', NULL, NULL, 'pending', '2026-03-13 06:52:31', '4', '90', '1170', '1170', '9967', 'GNF/323/25-26', '2026-03-15', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', 'DKT/CNB/01001', '2026-03-13', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Kanpur', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('9', 'DKT/CNB/01002', '2026-03-13', 'tbb', 'Kanpur', 'Noida', '11', '5', '12', '14 ft vehicle ', 'DL01LAE9272', 'SADANAND YADAV ', '9717026352', '7000', '61215', '2026-03-13', '411698168329', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'LEATHER SAFETY SHOES SAFARI PRO ART 1108', NULL, NULL, NULL, '2026-03-13 10:23:55', '4', '11', '143', '143', '9967', 'GNF/325/25-26', '2026-03-15', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01002', '2026-03-13', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Kanpur', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('13', 'DKT/CNB/01005', '2026-03-15', 'tbb', 'Kanpur', 'Noida', '11', '5', '12', '14 ft vehicle ', 'HR69F3379', 'AMAR SINGH ', ' 95579 96955', '7000', '265493', '2026-03-15', '451699038429', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'LEATHER SAFETY SHOES SAFARI PRO 1104 AND 1108', NULL, NULL, NULL, '2026-03-15 09:10:15', '4', '53', '690', '690', '9967', 'GNF/328/25-26', '2026-03-17', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01005', '2026-03-15', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Kanpur', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('14', 'DKT/CNB/01004', '2026-03-15', 'tbb', 'Kanpur', 'Noida', '11', '5', '12', '14 ft vehicle ', 'HR69F3379', 'AMAR SINGH ', ' 95579 96955', '7000', '55650', '2026-03-14', '491698656828', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, NULL, '2026-03-15 09:16:46', '6', '10', '130', '130', '9967', 'HLO/25-26/1302', '2026-03-16', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01004', '2026-03-15', '0', '0', '0', '6000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Kanpur', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('15', 'DKT/CNB/01003', '2026-03-15', 'tbb', 'Kanpur', 'Noida', '11', '5', '12', '14 ft vehicle ', 'HR69F3379', 'AMAR SINGH ', ' 95579 96955', '7000', '50973', '2026-03-14', '471698702943', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, NULL, '2026-03-15 09:20:37', '5', '17', '510', '510', '9967', 'TI/2217/25-26', '2026-03-16', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01003', '2026-03-15', '0', '0', '0', '6000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'Kanpur', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('16', 'DKT/CNB/01006', '2026-03-18', 'paid', 'KANPUR', 'BANDA ', '13', '6', '10', 'BOLERO PICKUP', 'UP78FN7972', 'RAJU', '8922924411', '0', '120000', '2026-03-18', '461700620019', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'OLD LIFT ', NULL, NULL, 'pending', '2026-03-18 16:42:40', '8', '1', '900', '900', '9967', 'DECLERATION LETTER', '2026-03-19', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', 'DKT/CNB/01006', '2026-03-18', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'BANDA ', '');
INSERT INTO `dockets` VALUES ('17', 'DKT/CNB/01007', '2026-03-19', 'tbb', 'KANPUR', 'Noida', '11', '7', '12', '14 ft vehicle ', 'MH43CE5486', 'DINESH RAJPOOT', '6392123483', '8000', '381150', '2026-03-19', '401700888578', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, NULL, '2026-03-19 09:34:12', '2', '66', '860', '860', '9967', '1642', '2026-03-21', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01007', '2026-03-19', '0', '0', '0', '7000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('18', 'DKT/CNB/01008', '2026-03-19', 'tbb', 'KANPUR', 'Noida', '11', '5', '12', '14 ft vehicle ', 'MH43CE5486', 'DINESH RAJPOOT', '6392123483', '8000', '312874', '2026-03-18', '451700621572', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, NULL, '2026-03-19 09:43:31', '4', '62', '820', '820', '9967', 'GNF/332/25-26', '2026-03-20', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01008', '2026-03-19', '0', '0', '0', '7000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'Noida', '');
INSERT INTO `dockets` VALUES ('19', 'DKT/CNB/01009', '2026-03-19', 'paid', '', '', '13', '8', '10', 'BOLERO PICKUP', 'UP78FN7972', 'Deepak', '+917800843866', '0', '150000', '2026-03-19', '491701151421', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'As per Declaration Letter ', NULL, NULL, 'pending', '2026-03-19 16:28:58', '9', '1', '1000', '1000', '9967', 'Declaration Letter ', '2026-03-20', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', 'DKT/CNB/01009', '2026-03-19', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'BANDA ', '', 'KANPUR', '');
INSERT INTO `dockets` VALUES ('20', 'DKT/CNB/01010', '2026-03-26', 'tbb', 'KANPUR', 'Panji', '11', '9', '12', '32 ft SXl container ', 'DL01LX5457', 'AMAR SINGH ', ' 95579 96955', '2000', '274050', '2026-03-26', '', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'Safety shoes ', NULL, NULL, 'paid', '2026-03-26 06:24:56', '2', '30', '390', '400', '9967', '1673', NULL, NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01010', '2026-03-26', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'Panji', '');
INSERT INTO `dockets` VALUES ('22', 'DKT/CNB/01011', '2026-04-09', 'paid', 'UNNAO', 'Faridabad', '14', '10', '12', 'BOLERO PICKUP', 'UP78CN7919', 'Vinod', '', '0', '165375', '2026-04-08', '', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, NULL, '2026-04-09 05:57:35', '10', '15', '195', '200', '9967', 'SCD2600016', NULL, NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '1', 'FTL', 'DKT/CNB/01011', '2026-04-09', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'UNNAO', '', 'Faridabad', '');
INSERT INTO `dockets` VALUES ('24', 'DKT/CNB/01012', '2026-04-09', 'tbb', 'KANPUR', 'Hazaribagh', '18', '11', '10', '22FT', 'DL01MB3213', 'MUNNA DAS', ' 8130541880 ', '36000', '1753416', '2026-04-09', '481710364062', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, 'pending', '2026-04-09 17:11:47', '11', '284', '4000', '4000', '9967', '25', '2026-04-13', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', 'DKT/CNB/01012', '2026-04-09', '0', '0', '0', '33000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'Hazaribagh', '');
INSERT INTO `dockets` VALUES ('25', 'DKT/CNB/01013', '2026-04-18', 'tbb', 'KANPUR', 'AMETHI', '9', '12', '10', 'TATA ACE', 'UP78GN2397', 'A.C.PAL', '9219617929', '5000', '170180', '2026-04-17', '411713942765', '4000', '0', '0', '0', '0', '0', '0', '4000.00', '0', '0.00', '4000.00', 'SAFETY SHOES ', NULL, NULL, 'paid', '2026-04-19 14:25:53', '3', '27', '400', '400', '9967', '786/ML/11', '2026-04-20', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Generated', '1', 'FTL', 'DKT/CNB/01013', '2026-04-18', '0', '0', '0', '4000', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'AMETHI', '');
INSERT INTO `dockets` VALUES ('26', '1022', '2026-05-19', 'tbb', 'KANPUR', 'KANPUR', '18', '13', '10', 'BOLERO PICKUP', 'UP16LT6455', 'ABHISHEK ', '9718581981', '5300', '367500', '2026-05-19', '461726579373', '0', '0', '0', '0', '0', '0', '0', '0.00', '0', '0.00', '0.00', 'SAFETY SHOES ', NULL, NULL, 'pending', '2026-05-20 05:05:44', '13', '50', '650', '650', '9967', '192', '2026-05-19', NULL, NULL, NULL, NULL, NULL, '0', '0', '0', '0', '0', '0', '0', 'Pending', '3', 'FTL', '1022', '2026-05-19', '0', '0', '0', '2800', '0', '0', '0', '0', '0', '0', '0', '2', '0', '0', NULL, NULL, 'City', 'City', 'KANPUR', '', 'KANPUR', '');

DROP TABLE IF EXISTS `docket_tracking`;
CREATE TABLE `docket_tracking` (
    id INTEGER,
    docket_id INT NOT NULL,
    status TEXT NOT NULL,
    location TEXT,
    location_name TEXT,
    reporting_date DATETIME,
    delivery_date DATETIME,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT,
    PRIMARY KEY (id)
);

INSERT INTO `docket_tracking` VALUES ('1', '6', 'created', NULL, NULL, NULL, NULL, NULL, '2026-02-28 07:09:41', 'Docket Created (FTL) at Delhi');
INSERT INTO `docket_tracking` VALUES ('2', '7', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-09 10:17:37', 'Docket Created (FTL) at Kanpur');
INSERT INTO `docket_tracking` VALUES ('3', '8', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-13 06:52:31', 'Docket Created (FTL) at Kanpur');
INSERT INTO `docket_tracking` VALUES ('4', '9', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-13 10:23:55', 'Docket Created (FTL) at Kanpur');
INSERT INTO `docket_tracking` VALUES ('8', '9', 'In Stock', NULL, 'Warehouse', NULL, NULL, 'OK', '2026-03-15 07:28:14', NULL);
INSERT INTO `docket_tracking` VALUES ('9', '13', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-15 09:10:15', 'Docket Created (FTL) at Kanpur');
INSERT INTO `docket_tracking` VALUES ('10', '14', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-15 09:16:46', 'Docket Created (FTL) at Kanpur');
INSERT INTO `docket_tracking` VALUES ('11', '15', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-15 09:20:37', 'Docket Created (FTL) at Kanpur');
INSERT INTO `docket_tracking` VALUES ('12', '16', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-18 16:42:40', 'Docket Created (FTL) at KANPUR');
INSERT INTO `docket_tracking` VALUES ('13', '17', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-19 09:34:12', 'Docket Created (FTL) at KANPUR');
INSERT INTO `docket_tracking` VALUES ('14', '18', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-19 09:43:31', 'Docket Created (FTL) at KANPUR');
INSERT INTO `docket_tracking` VALUES ('15', '19', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-19 16:28:58', 'Docket Created (FTL) at ');
INSERT INTO `docket_tracking` VALUES ('16', '20', 'created', NULL, NULL, NULL, NULL, NULL, '2026-03-26 06:24:56', 'Docket Created (FTL) at KANPUR');
INSERT INTO `docket_tracking` VALUES ('18', '22', 'created', NULL, NULL, NULL, NULL, NULL, '2026-04-09 05:57:35', 'Docket Created (FTL) at UNNAO');
INSERT INTO `docket_tracking` VALUES ('20', '24', 'created', NULL, NULL, NULL, NULL, NULL, '2026-04-09 17:11:47', 'Docket Created (FTL) at KANPUR');
INSERT INTO `docket_tracking` VALUES ('21', '25', 'created', NULL, NULL, NULL, NULL, NULL, '2026-04-19 14:25:53', 'Docket Created (FTL) at KANPUR');
INSERT INTO `docket_tracking` VALUES ('22', '25', 'delivered', NULL, NULL, NULL, NULL, NULL, '2026-05-10 10:30:21', 'Location: AMETHI. Notes:  | Delivered: 2026-05-10T21:02 | Waiting:  | Unloading Chg: 500 | Detention: 1000');
INSERT INTO `docket_tracking` VALUES ('23', '26', 'created', NULL, NULL, NULL, NULL, NULL, '2026-05-20 05:05:44', 'Docket Created (FTL) at KANPUR');

DROP TABLE IF EXISTS `pod_uploads`;
CREATE TABLE `pod_uploads` (
    id INTEGER,
    docket_id INT NOT NULL,
    file_name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `customer_bills`;
CREATE TABLE `customer_bills` (
    id INTEGER,
    bill_no TEXT NOT NULL,
    customer_id INT NOT NULL,
    bill_date DATETIME NOT NULL,
    total_amount VARCHAR(255) NOT NULL,
    status TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    subtotal VARCHAR(255) DEFAULT 0.00,
    gst_amount VARCHAR(255) DEFAULT 0.00,
    branch_id INTEGER DEFAULT 1,
    irn VARCHAR(255) DEFAULT NULL,
    signed_invoice TEXT DEFAULT NULL,
    signed_qr_code TEXT DEFAULT NULL,
    ack_no VARCHAR(255) DEFAULT NULL,
    ack_date DATETIME DEFAULT NULL,
    hsn_code VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (id)
);

INSERT INTO `customer_bills` VALUES ('1', 'INV/25-26/00046', '5', '2026-02-28', '52000', 'pending', '2026-02-28 18:28:50', '0.0', '0.0', '1', NULL, NULL, NULL, NULL, NULL, '996601');

DROP TABLE IF EXISTS `customer_bill_items`;
CREATE TABLE `customer_bill_items` (
    id INTEGER,
    bill_id INT NOT NULL,
    docket_id INT NOT NULL,
    freight_amount VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    subtotal VARCHAR(255) DEFAULT 0.00,
    gst_rate VARCHAR(255) DEFAULT 0.00,
    gst_amount VARCHAR(255) DEFAULT 0.00,
    total VARCHAR(255) DEFAULT 0.00,
    PRIMARY KEY (id)
);

INSERT INTO `customer_bill_items` VALUES ('1', '1', '6', '52000', '2026-02-28 18:28:50', '0.0', '0.0', '0.0', '0.0');

DROP TABLE IF EXISTS `bill_collections`;
CREATE TABLE `bill_collections` (
    id INTEGER,
    customer_id INT NOT NULL,
    bill_id INT NOT NULL,
    collection_date DATETIME NOT NULL,
    voucher_no TEXT,
    tds_amount VARCHAR(255) DEFAULT 0.00,
    collected_amount VARCHAR(255) NOT NULL,
    mamul_deduction VARCHAR(255) DEFAULT 0.00,
    balance_amount VARCHAR(255) NOT NULL,
    bank_id INT NOT NULL,
    utr_no TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    docket_id INT,
    narration TEXT,
    clear_date DATE DEFAULT NULL,
    remarks TEXT DEFAULT NULL,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `expense_payments`;
CREATE TABLE `expense_payments` (
    id INTEGER,
    payment_date DATETIME NOT NULL,
    voucher_no TEXT,
    vendor_id INT,
    expense_type_id INT NOT NULL,
    amount VARCHAR(255) NOT NULL,
    bank_id INT NOT NULL,
    utr_no TEXT,
    narration TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    clear_date DATE DEFAULT NULL,
    branch_id INTEGER DEFAULT 1,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `states`;
CREATE TABLE `states` (
    id INTEGER,
    name TEXT NOT NULL,
    code TEXT,
    PRIMARY KEY (id)
);

INSERT INTO `states` VALUES ('1', 'Andhra Pradesh', '37');
INSERT INTO `states` VALUES ('2', 'Arunachal Pradesh', '12');
INSERT INTO `states` VALUES ('3', 'Assam', '18');
INSERT INTO `states` VALUES ('4', 'Bihar', '10');
INSERT INTO `states` VALUES ('5', 'Chhattisgarh', '22');
INSERT INTO `states` VALUES ('6', 'Goa', '30');
INSERT INTO `states` VALUES ('7', 'Gujarat', '24');
INSERT INTO `states` VALUES ('8', 'Haryana', '06');
INSERT INTO `states` VALUES ('9', 'Himachal Pradesh', '02');
INSERT INTO `states` VALUES ('10', 'Jharkhand', '20');
INSERT INTO `states` VALUES ('11', 'Karnataka', '29');
INSERT INTO `states` VALUES ('12', 'Kerala', '32');
INSERT INTO `states` VALUES ('13', 'Madhya Pradesh', '23');
INSERT INTO `states` VALUES ('14', 'Maharashtra', '27');
INSERT INTO `states` VALUES ('15', 'Manipur', '14');
INSERT INTO `states` VALUES ('16', 'Meghalaya', '17');
INSERT INTO `states` VALUES ('17', 'Mizoram', '15');
INSERT INTO `states` VALUES ('18', 'Nagaland', '13');
INSERT INTO `states` VALUES ('19', 'Odisha', '21');
INSERT INTO `states` VALUES ('20', 'Punjab', '03');
INSERT INTO `states` VALUES ('21', 'Rajasthan', '08');
INSERT INTO `states` VALUES ('22', 'Sikkim', '11');
INSERT INTO `states` VALUES ('23', 'Tamil Nadu', '33');
INSERT INTO `states` VALUES ('24', 'Telangana', '36');
INSERT INTO `states` VALUES ('25', 'Tripura', '16');
INSERT INTO `states` VALUES ('26', 'Uttar Pradesh', '09');
INSERT INTO `states` VALUES ('27', 'Uttarakhand', '05');
INSERT INTO `states` VALUES ('28', 'West Bengal', '19');
INSERT INTO `states` VALUES ('29', 'Andaman and Nicobar Islands', '35');
INSERT INTO `states` VALUES ('30', 'Chandigarh', '04');
INSERT INTO `states` VALUES ('31', 'Dadra and Nagar Haveli and Daman and Diu', '26');
INSERT INTO `states` VALUES ('32', 'Delhi', '07');
INSERT INTO `states` VALUES ('33', 'Jammu and Kashmir', '01');
INSERT INTO `states` VALUES ('34', 'Ladakh', '38');
INSERT INTO `states` VALUES ('35', 'Lakshadweep', '31');
INSERT INTO `states` VALUES ('36', 'Puducherry', '34');

DROP TABLE IF EXISTS `cities`;
CREATE TABLE `cities` (
    id INTEGER,
    name TEXT NOT NULL,
    state_id INT NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO `cities` VALUES ('1', 'Visakhapatnam', '1');
INSERT INTO `cities` VALUES ('2', 'Vijayawada', '1');
INSERT INTO `cities` VALUES ('3', 'Guntur', '1');
INSERT INTO `cities` VALUES ('4', 'Nellore', '1');
INSERT INTO `cities` VALUES ('5', 'Kurnool', '1');
INSERT INTO `cities` VALUES ('6', 'Rajahmundry', '1');
INSERT INTO `cities` VALUES ('7', 'Kakinada', '1');
INSERT INTO `cities` VALUES ('8', 'Tirupati', '1');
INSERT INTO `cities` VALUES ('9', 'Anantapur', '1');
INSERT INTO `cities` VALUES ('10', 'Kadapa', '1');
INSERT INTO `cities` VALUES ('11', 'Vizianagaram', '1');
INSERT INTO `cities` VALUES ('12', 'Eluru', '1');
INSERT INTO `cities` VALUES ('13', 'Ongole', '1');
INSERT INTO `cities` VALUES ('14', 'Nandyal', '1');
INSERT INTO `cities` VALUES ('15', 'Machilipatnam', '1');
INSERT INTO `cities` VALUES ('16', 'Adoni', '1');
INSERT INTO `cities` VALUES ('17', 'Tenali', '1');
INSERT INTO `cities` VALUES ('18', 'Proddatur', '1');
INSERT INTO `cities` VALUES ('19', 'Chittoor', '1');
INSERT INTO `cities` VALUES ('20', 'Hindupur', '1');
INSERT INTO `cities` VALUES ('21', 'Bhimavaram', '1');
INSERT INTO `cities` VALUES ('22', 'Madanapalle', '1');
INSERT INTO `cities` VALUES ('23', 'Guntakal', '1');
INSERT INTO `cities` VALUES ('24', 'Dharmavaram', '1');
INSERT INTO `cities` VALUES ('25', 'Gudivada', '1');
INSERT INTO `cities` VALUES ('51', 'Guwahati', '3');
INSERT INTO `cities` VALUES ('52', 'Silchar', '3');
INSERT INTO `cities` VALUES ('53', 'Dibrugarh', '3');
INSERT INTO `cities` VALUES ('54', 'Jorhat', '3');
INSERT INTO `cities` VALUES ('55', 'Nagaon', '3');
INSERT INTO `cities` VALUES ('56', 'Tinsukia', '3');
INSERT INTO `cities` VALUES ('57', 'Tezpur', '3');
INSERT INTO `cities` VALUES ('58', 'Bongaigaon', '3');
INSERT INTO `cities` VALUES ('59', 'Dhubri', '3');
INSERT INTO `cities` VALUES ('60', 'Diphu', '3');
INSERT INTO `cities` VALUES ('61', 'North Lakhimpur', '3');
INSERT INTO `cities` VALUES ('62', 'Karimganj', '3');
INSERT INTO `cities` VALUES ('63', 'Sivasagar', '3');
INSERT INTO `cities` VALUES ('64', 'Goalpara', '3');
INSERT INTO `cities` VALUES ('65', 'Barpeta', '3');
INSERT INTO `cities` VALUES ('66', 'Lanka', '3');
INSERT INTO `cities` VALUES ('67', 'Lumding', '3');
INSERT INTO `cities` VALUES ('68', 'Mankachar', '3');
INSERT INTO `cities` VALUES ('69', 'Nalbari', '3');
INSERT INTO `cities` VALUES ('70', 'Rangia', '3');
INSERT INTO `cities` VALUES ('71', 'Margherita', '3');
INSERT INTO `cities` VALUES ('72', 'Mangaldoi', '3');
INSERT INTO `cities` VALUES ('73', 'Dergaon', '3');
INSERT INTO `cities` VALUES ('74', 'Naharkatia', '3');
INSERT INTO `cities` VALUES ('75', 'Kokrajhar', '3');
INSERT INTO `cities` VALUES ('76', 'Patna', '4');
INSERT INTO `cities` VALUES ('77', 'Gaya', '4');
INSERT INTO `cities` VALUES ('78', 'Bhagalpur', '4');
INSERT INTO `cities` VALUES ('79', 'Muzaffarpur', '4');
INSERT INTO `cities` VALUES ('80', 'Purnia', '4');
INSERT INTO `cities` VALUES ('81', 'Darbhanga', '4');
INSERT INTO `cities` VALUES ('82', 'Bihar Sharif', '4');
INSERT INTO `cities` VALUES ('83', 'Arrah', '4');
INSERT INTO `cities` VALUES ('84', 'Begusarai', '4');
INSERT INTO `cities` VALUES ('85', 'Katihar', '4');
INSERT INTO `cities` VALUES ('86', 'Munger', '4');
INSERT INTO `cities` VALUES ('87', 'Chhapra', '4');
INSERT INTO `cities` VALUES ('88', 'Danapur', '4');
INSERT INTO `cities` VALUES ('89', 'Bettiah', '4');
INSERT INTO `cities` VALUES ('90', 'Saharsa', '4');
INSERT INTO `cities` VALUES ('91', 'Sasaram', '4');
INSERT INTO `cities` VALUES ('92', 'Hajipur', '4');
INSERT INTO `cities` VALUES ('93', 'Dehri', '4');
INSERT INTO `cities` VALUES ('94', 'Siwan', '4');
INSERT INTO `cities` VALUES ('95', 'Motihari', '4');
INSERT INTO `cities` VALUES ('96', 'Nawada', '4');
INSERT INTO `cities` VALUES ('97', 'Bagaha', '4');
INSERT INTO `cities` VALUES ('98', 'Buxar', '4');
INSERT INTO `cities` VALUES ('99', 'Kishanganj', '4');
INSERT INTO `cities` VALUES ('100', 'Sitamarhi', '4');
INSERT INTO `cities` VALUES ('101', 'Raipur', '5');
INSERT INTO `cities` VALUES ('102', 'Bhilai', '5');
INSERT INTO `cities` VALUES ('103', 'Korba', '5');
INSERT INTO `cities` VALUES ('104', 'Bilaspur', '5');
INSERT INTO `cities` VALUES ('105', 'Rajnandgaon', '5');
INSERT INTO `cities` VALUES ('106', 'Raigarh', '5');
INSERT INTO `cities` VALUES ('107', 'Jagdalpur', '5');
INSERT INTO `cities` VALUES ('108', 'Ambikapur', '5');
INSERT INTO `cities` VALUES ('109', 'Dhamtari', '5');
INSERT INTO `cities` VALUES ('110', 'Chirmiri', '5');
INSERT INTO `cities` VALUES ('111', 'Bhatapara', '5');
INSERT INTO `cities` VALUES ('112', 'Dalli-Rajhara', '5');
INSERT INTO `cities` VALUES ('113', 'Naila Janjgir', '5');
INSERT INTO `cities` VALUES ('114', 'Kanker', '5');
INSERT INTO `cities` VALUES ('115', 'Mahasamund', '5');
INSERT INTO `cities` VALUES ('116', 'Dongargarh', '5');
INSERT INTO `cities` VALUES ('117', 'Baikunthpur', '5');
INSERT INTO `cities` VALUES ('118', 'Tilda Newra', '5');
INSERT INTO `cities` VALUES ('119', 'Mungeli', '5');
INSERT INTO `cities` VALUES ('120', 'Manendragarh', '5');
INSERT INTO `cities` VALUES ('121', 'Sakti', '5');
INSERT INTO `cities` VALUES ('122', 'Kondagaon', '5');
INSERT INTO `cities` VALUES ('123', 'Gobranawapara', '5');
INSERT INTO `cities` VALUES ('124', 'Bemetara', '5');
INSERT INTO `cities` VALUES ('125', 'Kawardha', '5');
INSERT INTO `cities` VALUES ('151', 'Ahmedabad', '7');
INSERT INTO `cities` VALUES ('152', 'Surat', '7');
INSERT INTO `cities` VALUES ('153', 'Vadodara', '7');
INSERT INTO `cities` VALUES ('154', 'Rajkot', '7');
INSERT INTO `cities` VALUES ('155', 'Bhavnagar', '7');
INSERT INTO `cities` VALUES ('156', 'Jamnagar', '7');
INSERT INTO `cities` VALUES ('157', 'Junagadh', '7');
INSERT INTO `cities` VALUES ('158', 'Gandhinagar', '7');
INSERT INTO `cities` VALUES ('159', 'Anand', '7');
INSERT INTO `cities` VALUES ('160', 'Navsari', '7');
INSERT INTO `cities` VALUES ('161', 'Morbi', '7');
INSERT INTO `cities` VALUES ('162', 'Nadiad', '7');
INSERT INTO `cities` VALUES ('163', 'Surendranagar', '7');
INSERT INTO `cities` VALUES ('164', 'Bharuch', '7');
INSERT INTO `cities` VALUES ('165', 'Mehsana', '7');
INSERT INTO `cities` VALUES ('166', 'Bhuj', '7');
INSERT INTO `cities` VALUES ('167', 'Porbandar', '7');
INSERT INTO `cities` VALUES ('168', 'Palanpur', '7');
INSERT INTO `cities` VALUES ('169', 'Valsad', '7');
INSERT INTO `cities` VALUES ('170', 'Vapi', '7');
INSERT INTO `cities` VALUES ('171', 'Gondal', '7');
INSERT INTO `cities` VALUES ('172', 'Veraval', '7');
INSERT INTO `cities` VALUES ('173', 'Godhra', '7');
INSERT INTO `cities` VALUES ('174', 'Patan', '7');
INSERT INTO `cities` VALUES ('175', 'Kalol', '7');
INSERT INTO `cities` VALUES ('176', 'Faridabad', '8');
INSERT INTO `cities` VALUES ('177', 'Gurugram', '8');
INSERT INTO `cities` VALUES ('178', 'Panipat', '8');
INSERT INTO `cities` VALUES ('179', 'Ambala', '8');
INSERT INTO `cities` VALUES ('180', 'Yamunanagar', '8');
INSERT INTO `cities` VALUES ('181', 'Rohtak', '8');
INSERT INTO `cities` VALUES ('182', 'Hisar', '8');
INSERT INTO `cities` VALUES ('183', 'Karnal', '8');
INSERT INTO `cities` VALUES ('184', 'Sonipat', '8');
INSERT INTO `cities` VALUES ('185', 'Panchkula', '8');
INSERT INTO `cities` VALUES ('186', 'Bhiwani', '8');
INSERT INTO `cities` VALUES ('187', 'Sirsa', '8');
INSERT INTO `cities` VALUES ('188', 'Bahadurgarh', '8');
INSERT INTO `cities` VALUES ('189', 'Jind', '8');
INSERT INTO `cities` VALUES ('190', 'Thanesar', '8');
INSERT INTO `cities` VALUES ('191', 'Kaithal', '8');
INSERT INTO `cities` VALUES ('192', 'Rewari', '8');
INSERT INTO `cities` VALUES ('193', 'Palwal', '8');
INSERT INTO `cities` VALUES ('194', 'Hansi', '8');
INSERT INTO `cities` VALUES ('195', 'Narnaul', '8');
INSERT INTO `cities` VALUES ('196', 'Fatehabad', '8');
INSERT INTO `cities` VALUES ('197', 'Gohana', '8');
INSERT INTO `cities` VALUES ('198', 'Tohana', '8');
INSERT INTO `cities` VALUES ('199', 'Narwana', '8');
INSERT INTO `cities` VALUES ('200', 'Mandi Dabwali', '8');
INSERT INTO `cities` VALUES ('226', 'Ranchi', '10');
INSERT INTO `cities` VALUES ('227', 'Jamshedpur', '10');
INSERT INTO `cities` VALUES ('228', 'Dhanbad', '10');
INSERT INTO `cities` VALUES ('229', 'Bokaro Steel City', '10');
INSERT INTO `cities` VALUES ('230', 'Deoghar', '10');
INSERT INTO `cities` VALUES ('231', 'Phusro', '10');
INSERT INTO `cities` VALUES ('232', 'Hazaribagh', '10');
INSERT INTO `cities` VALUES ('233', 'Giridih', '10');
INSERT INTO `cities` VALUES ('234', 'Ramgarh', '10');
INSERT INTO `cities` VALUES ('235', 'Medininagar', '10');
INSERT INTO `cities` VALUES ('236', 'Chirkunda', '10');
INSERT INTO `cities` VALUES ('237', 'Jhumri Tilaiya', '10');
INSERT INTO `cities` VALUES ('238', 'Sahibganj', '10');
INSERT INTO `cities` VALUES ('239', 'Chaibasa', '10');
INSERT INTO `cities` VALUES ('240', 'Lohardaga', '10');
INSERT INTO `cities` VALUES ('241', 'Chakradharpur', '10');
INSERT INTO `cities` VALUES ('242', 'Madhupur', '10');
INSERT INTO `cities` VALUES ('243', 'Gumla', '10');
INSERT INTO `cities` VALUES ('244', 'Simdega', '10');
INSERT INTO `cities` VALUES ('245', 'Dumka', '10');
INSERT INTO `cities` VALUES ('246', 'Garhwa', '10');
INSERT INTO `cities` VALUES ('247', 'Pakur', '10');
INSERT INTO `cities` VALUES ('248', 'Jamtara', '10');
INSERT INTO `cities` VALUES ('249', 'Latehar', '10');
INSERT INTO `cities` VALUES ('250', 'Godda', '10');
INSERT INTO `cities` VALUES ('251', 'Bengaluru', '11');
INSERT INTO `cities` VALUES ('252', 'Mysuru', '11');
INSERT INTO `cities` VALUES ('253', 'Hubballi-Dharwad', '11');
INSERT INTO `cities` VALUES ('254', 'Mangaluru', '11');
INSERT INTO `cities` VALUES ('255', 'Belagavi', '11');
INSERT INTO `cities` VALUES ('256', 'Kalaburagi', '11');
INSERT INTO `cities` VALUES ('257', 'Davanagere', '11');
INSERT INTO `cities` VALUES ('258', 'Ballari', '11');
INSERT INTO `cities` VALUES ('259', 'Vijayapura', '11');
INSERT INTO `cities` VALUES ('260', 'Shivamogga', '11');
INSERT INTO `cities` VALUES ('261', 'Tumakuru', '11');
INSERT INTO `cities` VALUES ('262', 'Raichur', '11');
INSERT INTO `cities` VALUES ('263', 'Bidar', '11');
INSERT INTO `cities` VALUES ('264', 'Hospet', '11');
INSERT INTO `cities` VALUES ('265', 'Hassan', '11');
INSERT INTO `cities` VALUES ('266', 'Gadag-Betigeri', '11');
INSERT INTO `cities` VALUES ('267', 'Udupi', '11');
INSERT INTO `cities` VALUES ('268', 'Robertsonpet', '11');
INSERT INTO `cities` VALUES ('269', 'Bhadravati', '11');
INSERT INTO `cities` VALUES ('270', 'Chitradurga', '11');
INSERT INTO `cities` VALUES ('271', 'Kolar', '11');
INSERT INTO `cities` VALUES ('272', 'Mandya', '11');
INSERT INTO `cities` VALUES ('273', 'Chikkamagaluru', '11');
INSERT INTO `cities` VALUES ('274', 'Gangavati', '11');
INSERT INTO `cities` VALUES ('275', 'Bagalkot', '11');
INSERT INTO `cities` VALUES ('276', 'Thiruvananthapuram', '12');
INSERT INTO `cities` VALUES ('277', 'Kochi', '12');
INSERT INTO `cities` VALUES ('278', 'Kozhikode', '12');
INSERT INTO `cities` VALUES ('279', 'Kollam', '12');
INSERT INTO `cities` VALUES ('280', 'Thrissur', '12');
INSERT INTO `cities` VALUES ('281', 'Palakkad', '12');
INSERT INTO `cities` VALUES ('282', 'Alappuzha', '12');
INSERT INTO `cities` VALUES ('283', 'Kannur', '12');
INSERT INTO `cities` VALUES ('284', 'Kottayam', '12');
INSERT INTO `cities` VALUES ('285', 'Kasaragod', '12');
INSERT INTO `cities` VALUES ('286', 'Malappuram', '12');
INSERT INTO `cities` VALUES ('287', 'Thalassery', '12');
INSERT INTO `cities` VALUES ('288', 'Ponnani', '12');
INSERT INTO `cities` VALUES ('289', 'Vatakara', '12');
INSERT INTO `cities` VALUES ('290', 'Kanhangad', '12');
INSERT INTO `cities` VALUES ('291', 'Payyanur', '12');
INSERT INTO `cities` VALUES ('292', 'Koyilandy', '12');
INSERT INTO `cities` VALUES ('293', 'Parappanangadi', '12');
INSERT INTO `cities` VALUES ('294', 'Kalamassery', '12');
INSERT INTO `cities` VALUES ('295', 'Kodungallur', '12');
INSERT INTO `cities` VALUES ('296', 'Kunnamkulam', '12');
INSERT INTO `cities` VALUES ('297', 'Attingal', '12');
INSERT INTO `cities` VALUES ('298', 'Chittur-Thathamangalam', '12');
INSERT INTO `cities` VALUES ('299', 'Muvattupuzha', '12');
INSERT INTO `cities` VALUES ('300', 'Adoor', '12');
INSERT INTO `cities` VALUES ('301', 'Indore', '13');
INSERT INTO `cities` VALUES ('302', 'Bhopal', '13');
INSERT INTO `cities` VALUES ('303', 'Jabalpur', '13');
INSERT INTO `cities` VALUES ('304', 'Gwalior', '13');
INSERT INTO `cities` VALUES ('305', 'Ujjain', '13');
INSERT INTO `cities` VALUES ('306', 'Sagar', '13');
INSERT INTO `cities` VALUES ('307', 'Dewas', '13');
INSERT INTO `cities` VALUES ('308', 'Satna', '13');
INSERT INTO `cities` VALUES ('309', 'Ratlam', '13');
INSERT INTO `cities` VALUES ('310', 'Rewa', '13');
INSERT INTO `cities` VALUES ('311', 'Murwara', '13');
INSERT INTO `cities` VALUES ('312', 'Singrauli', '13');
INSERT INTO `cities` VALUES ('313', 'Burhanpur', '13');
INSERT INTO `cities` VALUES ('314', 'Khandwa', '13');
INSERT INTO `cities` VALUES ('315', 'Bhind', '13');
INSERT INTO `cities` VALUES ('316', 'Chhindwara', '13');
INSERT INTO `cities` VALUES ('317', 'Guna', '13');
INSERT INTO `cities` VALUES ('318', 'Shivpuri', '13');
INSERT INTO `cities` VALUES ('319', 'Vidisha', '13');
INSERT INTO `cities` VALUES ('320', 'Chhatarpur', '13');
INSERT INTO `cities` VALUES ('321', 'Damoh', '13');
INSERT INTO `cities` VALUES ('322', 'Mandsaur', '13');
INSERT INTO `cities` VALUES ('323', 'Khargone', '13');
INSERT INTO `cities` VALUES ('324', 'Neemuch', '13');
INSERT INTO `cities` VALUES ('325', 'Pithampur', '13');
INSERT INTO `cities` VALUES ('326', 'Mumbai', '14');
INSERT INTO `cities` VALUES ('327', 'Pune', '14');
INSERT INTO `cities` VALUES ('328', 'Nagpur', '14');
INSERT INTO `cities` VALUES ('329', 'Thane', '14');
INSERT INTO `cities` VALUES ('330', 'Nashik', '14');
INSERT INTO `cities` VALUES ('331', 'Aurangabad', '14');
INSERT INTO `cities` VALUES ('332', 'Solapur', '14');
INSERT INTO `cities` VALUES ('333', 'Amravati', '14');
INSERT INTO `cities` VALUES ('334', 'Nanded', '14');
INSERT INTO `cities` VALUES ('335', 'Kolhapur', '14');
INSERT INTO `cities` VALUES ('336', 'Sangli', '14');
INSERT INTO `cities` VALUES ('337', 'Jalgaon', '14');
INSERT INTO `cities` VALUES ('338', 'Akola', '14');
INSERT INTO `cities` VALUES ('339', 'Latur', '14');
INSERT INTO `cities` VALUES ('340', 'Dhule', '14');
INSERT INTO `cities` VALUES ('341', 'Ahmednagar', '14');
INSERT INTO `cities` VALUES ('342', 'Chandrapur', '14');
INSERT INTO `cities` VALUES ('343', 'Parbhani', '14');
INSERT INTO `cities` VALUES ('344', 'Ichalkaranji', '14');
INSERT INTO `cities` VALUES ('345', 'Jalna', '14');
INSERT INTO `cities` VALUES ('346', 'Bhusawal', '14');
INSERT INTO `cities` VALUES ('347', 'Navi Mumbai', '14');
INSERT INTO `cities` VALUES ('348', 'Panvel', '14');
INSERT INTO `cities` VALUES ('349', 'Satara', '14');
INSERT INTO `cities` VALUES ('350', 'Beed', '14');
INSERT INTO `cities` VALUES ('451', 'Bhubaneswar', '19');
INSERT INTO `cities` VALUES ('452', 'Cuttack', '19');
INSERT INTO `cities` VALUES ('453', 'Rourkela', '19');
INSERT INTO `cities` VALUES ('454', 'Berhampur', '19');
INSERT INTO `cities` VALUES ('455', 'Sambalpur', '19');
INSERT INTO `cities` VALUES ('456', 'Puri', '19');
INSERT INTO `cities` VALUES ('457', 'Balasore', '19');
INSERT INTO `cities` VALUES ('458', 'Bhadrak', '19');
INSERT INTO `cities` VALUES ('459', 'Baripada', '19');
INSERT INTO `cities` VALUES ('460', 'Jharsuguda', '19');
INSERT INTO `cities` VALUES ('461', 'Jeypore', '19');
INSERT INTO `cities` VALUES ('462', 'Bargarh', '19');
INSERT INTO `cities` VALUES ('463', 'Rayagada', '19');
INSERT INTO `cities` VALUES ('464', 'Bolangir', '19');
INSERT INTO `cities` VALUES ('465', 'Kendujhar', '19');
INSERT INTO `cities` VALUES ('466', 'Angul', '19');
INSERT INTO `cities` VALUES ('467', 'Paradip', '19');
INSERT INTO `cities` VALUES ('468', 'Bhawanipatna', '19');
INSERT INTO `cities` VALUES ('469', 'Dhenkanal', '19');
INSERT INTO `cities` VALUES ('470', 'Barbil', '19');
INSERT INTO `cities` VALUES ('471', 'Jajpur', '19');
INSERT INTO `cities` VALUES ('472', 'Sunabeda', '19');
INSERT INTO `cities` VALUES ('473', 'Talcher', '19');
INSERT INTO `cities` VALUES ('474', 'Byasanagar', '19');
INSERT INTO `cities` VALUES ('475', 'Kendrapara', '19');
INSERT INTO `cities` VALUES ('476', 'Ludhiana', '20');
INSERT INTO `cities` VALUES ('477', 'Amritsar', '20');
INSERT INTO `cities` VALUES ('478', 'Jalandhar', '20');
INSERT INTO `cities` VALUES ('479', 'Patiala', '20');
INSERT INTO `cities` VALUES ('480', 'Bathinda', '20');
INSERT INTO `cities` VALUES ('481', 'Hoshiarpur', '20');
INSERT INTO `cities` VALUES ('482', 'Mohali', '20');
INSERT INTO `cities` VALUES ('483', 'Batala', '20');
INSERT INTO `cities` VALUES ('484', 'Pathankot', '20');
INSERT INTO `cities` VALUES ('485', 'Moga', '20');
INSERT INTO `cities` VALUES ('486', 'Abohar', '20');
INSERT INTO `cities` VALUES ('487', 'Malerkotla', '20');
INSERT INTO `cities` VALUES ('488', 'Khanna', '20');
INSERT INTO `cities` VALUES ('489', 'Phagwara', '20');
INSERT INTO `cities` VALUES ('490', 'Muktsar', '20');
INSERT INTO `cities` VALUES ('491', 'Barnala', '20');
INSERT INTO `cities` VALUES ('492', 'Rajpura', '20');
INSERT INTO `cities` VALUES ('493', 'Firozpur', '20');
INSERT INTO `cities` VALUES ('494', 'Kapurthala', '20');
INSERT INTO `cities` VALUES ('495', 'Sunam', '20');
INSERT INTO `cities` VALUES ('496', 'Kot Kapura', '20');
INSERT INTO `cities` VALUES ('497', 'Fazilka', '20');
INSERT INTO `cities` VALUES ('498', 'Gurdaspur', '20');
INSERT INTO `cities` VALUES ('499', 'Sangrur', '20');
INSERT INTO `cities` VALUES ('500', 'Mansa', '20');
INSERT INTO `cities` VALUES ('501', 'Jaipur', '21');
INSERT INTO `cities` VALUES ('502', 'Jodhpur', '21');
INSERT INTO `cities` VALUES ('503', 'Kota', '21');
INSERT INTO `cities` VALUES ('504', 'Bikaner', '21');
INSERT INTO `cities` VALUES ('505', 'Ajmer', '21');
INSERT INTO `cities` VALUES ('506', 'Udaipur', '21');
INSERT INTO `cities` VALUES ('507', 'Bhilwara', '21');
INSERT INTO `cities` VALUES ('508', 'Alwar', '21');
INSERT INTO `cities` VALUES ('509', 'Bharatpur', '21');
INSERT INTO `cities` VALUES ('510', 'Sri Ganganagar', '21');
INSERT INTO `cities` VALUES ('511', 'Sikar', '21');
INSERT INTO `cities` VALUES ('512', 'Pali', '21');
INSERT INTO `cities` VALUES ('513', 'Tonk', '21');
INSERT INTO `cities` VALUES ('514', 'Kishangarh', '21');
INSERT INTO `cities` VALUES ('515', 'Beawar', '21');
INSERT INTO `cities` VALUES ('516', 'Hanumangarh', '21');
INSERT INTO `cities` VALUES ('517', 'Dhaulpur', '21');
INSERT INTO `cities` VALUES ('518', 'Sawai Madhopur', '21');
INSERT INTO `cities` VALUES ('519', 'Churu', '21');
INSERT INTO `cities` VALUES ('520', 'Gangapur City', '21');
INSERT INTO `cities` VALUES ('521', 'Jhunjhunu', '21');
INSERT INTO `cities` VALUES ('522', 'Baran', '21');
INSERT INTO `cities` VALUES ('523', 'Chittorgarh', '21');
INSERT INTO `cities` VALUES ('524', 'Makrana', '21');
INSERT INTO `cities` VALUES ('525', 'Nagaur', '21');
INSERT INTO `cities` VALUES ('551', 'Chennai', '23');
INSERT INTO `cities` VALUES ('552', 'Coimbatore', '23');
INSERT INTO `cities` VALUES ('553', 'Madurai', '23');
INSERT INTO `cities` VALUES ('554', 'Tiruchirappalli', '23');
INSERT INTO `cities` VALUES ('555', 'Tiruppur', '23');
INSERT INTO `cities` VALUES ('556', 'Salem', '23');
INSERT INTO `cities` VALUES ('557', 'Erode', '23');
INSERT INTO `cities` VALUES ('558', 'Tirunelveli', '23');
INSERT INTO `cities` VALUES ('559', 'Vellore', '23');
INSERT INTO `cities` VALUES ('560', 'Thoothukkudi', '23');
INSERT INTO `cities` VALUES ('561', 'Dindigul', '23');
INSERT INTO `cities` VALUES ('562', 'Thanjavur', '23');
INSERT INTO `cities` VALUES ('563', 'Ranipet', '23');
INSERT INTO `cities` VALUES ('564', 'Sivakasi', '23');
INSERT INTO `cities` VALUES ('565', 'Karur', '23');
INSERT INTO `cities` VALUES ('566', 'Udhagamandalam', '23');
INSERT INTO `cities` VALUES ('567', 'Hosur', '23');
INSERT INTO `cities` VALUES ('568', 'Nagercoil', '23');
INSERT INTO `cities` VALUES ('569', 'Kancheepuram', '23');
INSERT INTO `cities` VALUES ('570', 'Kumarapalayam', '23');
INSERT INTO `cities` VALUES ('571', 'Karaikudi', '23');
INSERT INTO `cities` VALUES ('572', 'Neyveli', '23');
INSERT INTO `cities` VALUES ('573', 'Cuddalore', '23');
INSERT INTO `cities` VALUES ('574', 'Kumbakonam', '23');
INSERT INTO `cities` VALUES ('575', 'Tiruvannamalai', '23');
INSERT INTO `cities` VALUES ('576', 'Hyderabad', '24');
INSERT INTO `cities` VALUES ('577', 'Warangal', '24');
INSERT INTO `cities` VALUES ('578', 'Nizamabad', '24');
INSERT INTO `cities` VALUES ('579', 'Khammam', '24');
INSERT INTO `cities` VALUES ('580', 'Karimnagar', '24');
INSERT INTO `cities` VALUES ('581', 'Ramagundam', '24');
INSERT INTO `cities` VALUES ('582', 'Mahbubnagar', '24');
INSERT INTO `cities` VALUES ('583', 'Nalgonda', '24');
INSERT INTO `cities` VALUES ('584', 'Adilabad', '24');
INSERT INTO `cities` VALUES ('585', 'Suryapet', '24');
INSERT INTO `cities` VALUES ('586', 'Miryalaguda', '24');
INSERT INTO `cities` VALUES ('587', 'Jagtial', '24');
INSERT INTO `cities` VALUES ('588', 'Nirmal', '24');
INSERT INTO `cities` VALUES ('589', 'Kamareddy', '24');
INSERT INTO `cities` VALUES ('590', 'Kothagudem', '24');
INSERT INTO `cities` VALUES ('591', 'Bodhan', '24');
INSERT INTO `cities` VALUES ('592', 'Palwancha', '24');
INSERT INTO `cities` VALUES ('593', 'Mandamarri', '24');
INSERT INTO `cities` VALUES ('594', 'Koratla', '24');
INSERT INTO `cities` VALUES ('595', 'Sircilla', '24');
INSERT INTO `cities` VALUES ('596', 'Tandur', '24');
INSERT INTO `cities` VALUES ('597', 'Siddipet', '24');
INSERT INTO `cities` VALUES ('598', 'Wanaparthy', '24');
INSERT INTO `cities` VALUES ('599', 'Gadwal', '24');
INSERT INTO `cities` VALUES ('600', 'Mancherial', '24');
INSERT INTO `cities` VALUES ('626', 'Lucknow', '26');
INSERT INTO `cities` VALUES ('627', 'Kanpur', '26');
INSERT INTO `cities` VALUES ('628', 'Ghaziabad', '26');
INSERT INTO `cities` VALUES ('629', 'Agra', '26');
INSERT INTO `cities` VALUES ('630', 'Meerut', '26');
INSERT INTO `cities` VALUES ('631', 'Varanasi', '26');
INSERT INTO `cities` VALUES ('632', 'Prayagraj', '26');
INSERT INTO `cities` VALUES ('633', 'Bareilly', '26');
INSERT INTO `cities` VALUES ('634', 'Aligarh', '26');
INSERT INTO `cities` VALUES ('635', 'Moradabad', '26');
INSERT INTO `cities` VALUES ('636', 'Saharanpur', '26');
INSERT INTO `cities` VALUES ('637', 'Gorakhpur', '26');
INSERT INTO `cities` VALUES ('638', 'Noida', '26');
INSERT INTO `cities` VALUES ('639', 'Firozabad', '26');
INSERT INTO `cities` VALUES ('640', 'Jhansi', '26');
INSERT INTO `cities` VALUES ('641', 'Muzaffarnagar', '26');
INSERT INTO `cities` VALUES ('642', 'Mathura', '26');
INSERT INTO `cities` VALUES ('643', 'Ayodhya', '26');
INSERT INTO `cities` VALUES ('644', 'Rampur', '26');
INSERT INTO `cities` VALUES ('645', 'Shahjahanpur', '26');
INSERT INTO `cities` VALUES ('646', 'Farrukhabad', '26');
INSERT INTO `cities` VALUES ('647', 'Maunath Bhanjan', '26');
INSERT INTO `cities` VALUES ('648', 'Hapur', '26');
INSERT INTO `cities` VALUES ('649', 'Etawah', '26');
INSERT INTO `cities` VALUES ('650', 'Mirzapur', '26');
INSERT INTO `cities` VALUES ('651', 'Dehradun', '27');
INSERT INTO `cities` VALUES ('652', 'Haridwar', '27');
INSERT INTO `cities` VALUES ('653', 'Roorkee', '27');
INSERT INTO `cities` VALUES ('654', 'Haldwani', '27');
INSERT INTO `cities` VALUES ('655', 'Rudrapur', '27');
INSERT INTO `cities` VALUES ('656', 'Kashipur', '27');
INSERT INTO `cities` VALUES ('657', 'Rishikesh', '27');
INSERT INTO `cities` VALUES ('658', 'Ramnagar', '27');
INSERT INTO `cities` VALUES ('659', 'Pithoragarh', '27');
INSERT INTO `cities` VALUES ('660', 'Manglaur', '27');
INSERT INTO `cities` VALUES ('661', 'Jaspur', '27');
INSERT INTO `cities` VALUES ('662', 'Nainital', '27');
INSERT INTO `cities` VALUES ('663', 'Almora', '27');
INSERT INTO `cities` VALUES ('664', 'Mussoorie', '27');
INSERT INTO `cities` VALUES ('665', 'Pauri', '27');
INSERT INTO `cities` VALUES ('666', 'Srinagar', '27');
INSERT INTO `cities` VALUES ('667', 'Kichha', '27');
INSERT INTO `cities` VALUES ('668', 'Laksar', '27');
INSERT INTO `cities` VALUES ('669', 'Sitarganj', '27');
INSERT INTO `cities` VALUES ('670', 'Bazpur', '27');
INSERT INTO `cities` VALUES ('671', 'Kotdwar', '27');
INSERT INTO `cities` VALUES ('672', 'Tehri', '27');
INSERT INTO `cities` VALUES ('673', 'Chamba', '27');
INSERT INTO `cities` VALUES ('674', 'Bageshwar', '27');
INSERT INTO `cities` VALUES ('675', 'Champawat', '27');
INSERT INTO `cities` VALUES ('676', 'Kolkata', '28');
INSERT INTO `cities` VALUES ('677', 'Asansol', '28');
INSERT INTO `cities` VALUES ('678', 'Siliguri', '28');
INSERT INTO `cities` VALUES ('679', 'Durgapur', '28');
INSERT INTO `cities` VALUES ('680', 'Bardhaman', '28');
INSERT INTO `cities` VALUES ('681', 'Malda', '28');
INSERT INTO `cities` VALUES ('682', 'Baharampur', '28');
INSERT INTO `cities` VALUES ('683', 'Habra', '28');
INSERT INTO `cities` VALUES ('684', 'Kharagpur', '28');
INSERT INTO `cities` VALUES ('685', 'Shantipur', '28');
INSERT INTO `cities` VALUES ('686', 'Dankuni', '28');
INSERT INTO `cities` VALUES ('687', 'Dhulian', '28');
INSERT INTO `cities` VALUES ('688', 'Ranaghat', '28');
INSERT INTO `cities` VALUES ('689', 'Haldia', '28');
INSERT INTO `cities` VALUES ('690', 'Raiganj', '28');
INSERT INTO `cities` VALUES ('691', 'Krishnanagar', '28');
INSERT INTO `cities` VALUES ('692', 'Nabadwip', '28');
INSERT INTO `cities` VALUES ('693', 'Medinipur', '28');
INSERT INTO `cities` VALUES ('694', 'Jalpaiguri', '28');
INSERT INTO `cities` VALUES ('695', 'Balurghat', '28');
INSERT INTO `cities` VALUES ('696', 'Basirhat', '28');
INSERT INTO `cities` VALUES ('697', 'Bankura', '28');
INSERT INTO `cities` VALUES ('698', 'Chakdaha', '28');
INSERT INTO `cities` VALUES ('699', 'Darjeeling', '28');
INSERT INTO `cities` VALUES ('700', 'Alipurduar', '28');
INSERT INTO `cities` VALUES ('776', 'New Delhi', '32');
INSERT INTO `cities` VALUES ('777', 'Delhi', '32');
INSERT INTO `cities` VALUES ('778', 'North Delhi', '32');
INSERT INTO `cities` VALUES ('779', 'North West Delhi', '32');
INSERT INTO `cities` VALUES ('780', 'West Delhi', '32');
INSERT INTO `cities` VALUES ('781', 'South West Delhi', '32');
INSERT INTO `cities` VALUES ('782', 'South Delhi', '32');
INSERT INTO `cities` VALUES ('783', 'South East Delhi', '32');
INSERT INTO `cities` VALUES ('784', 'Central Delhi', '32');
INSERT INTO `cities` VALUES ('785', 'North East Delhi', '32');
INSERT INTO `cities` VALUES ('786', 'Shahdara', '32');
INSERT INTO `cities` VALUES ('787', 'East Delhi', '32');
INSERT INTO `cities` VALUES ('788', 'Dwarka', '32');
INSERT INTO `cities` VALUES ('789', 'Rohini', '32');
INSERT INTO `cities` VALUES ('790', 'Pitampura', '32');
INSERT INTO `cities` VALUES ('791', 'Janakpuri', '32');
INSERT INTO `cities` VALUES ('792', 'Saket', '32');
INSERT INTO `cities` VALUES ('793', 'Vasant Kunj', '32');
INSERT INTO `cities` VALUES ('794', 'Hauz Khas', '32');
INSERT INTO `cities` VALUES ('795', 'Lajpat Nagar', '32');
INSERT INTO `cities` VALUES ('796', 'Karol Bagh', '32');
INSERT INTO `cities` VALUES ('797', 'Connaught Place', '32');
INSERT INTO `cities` VALUES ('798', 'Chanakyapuri', '32');
INSERT INTO `cities` VALUES ('799', 'Okhla', '32');
INSERT INTO `cities` VALUES ('800', 'Nehru Place', '32');
INSERT INTO `cities` VALUES ('801', 'MUMBAI', '14');
INSERT INTO `cities` VALUES ('802', 'KANPUR', '26');
INSERT INTO `cities` VALUES ('803', 'BANDA ', '26');
INSERT INTO `cities` VALUES ('804', 'Panji', '6');
INSERT INTO `cities` VALUES ('805', 'UNNAO', '26');
INSERT INTO `cities` VALUES ('806', 'AMETHI', '26');

DROP TABLE IF EXISTS `consignors`;
CREATE TABLE `consignors` (
    id INTEGER,
    code TEXT,
    name TEXT NOT NULL,
    address TEXT,
    city_id INT,
    phone TEXT,
    email TEXT,
    gst TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `consignors` VALUES ('1', 'DEL-C-001', 'KAMAL KUMAR MISHRA', '730
eros corporate park', '4', '07695065498', 'kamalkmishra8@gmail.com', '24KULPS0110J1ZJ', '2026-02-26 18:12:55');
INSERT INTO `consignors` VALUES ('3', 'MAS-C-001', 'MASH & COMPANY ', '87/73-B HINDUSTAN TANNERY COMPOUND JAJMAU KANPUR -208011', '627', '8887664072', 'info@mashgroup.in', '09AABFM4240M1Z6', '2026-03-09 08:59:11');
INSERT INTO `consignors` VALUES ('4', 'KNP', 'G.N.FOOTWEAR ', '33/29 LARI COMPOUND GAJJU PURWA JAJMAU KANPUR', '627', '09990848912', 'g.n.leather111@gmail.com', '09DZYPK5834C1ZT', '2026-03-13 06:38:24');
INSERT INTO `consignors` VALUES ('5', 'SOU-C-001', 'GLOBE INDUSTRIES', 'PLOT NO. 37-B 150 FT ROAD JAJMAU
KANPUR
', '627', '6386887985', 'giaccount@globegroups.in', ' 09AAEFG9214C1ZM', '2026-03-15 07:08:31');
INSERT INTO `consignors` VALUES ('6', 'SOU-C-002', 'HIGH LITE OVERSEAS', 'Plot No. 22, Arazi No 213, Near Guru HarRai Academy
Sanigawan Road, Daheli Sujanpur, Kanpur-208021', '627', '7007807117', 'highlite2018@gmail.com', '09FSGPS7109F1ZD', '2026-03-15 07:09:45');
INSERT INTO `consignors` VALUES ('7', 'SUP-C-001', 'SUPER WHEELS UTHORISED DEALERS BAJAJ AUTO LTD', '125/5 U BLOCK GOVIND NAGAR CTI CHAURAHA KANPUR NAGAR', '802', '', 'moveup19kanpur@gmail.com', '', '2026-03-18 16:32:40');
INSERT INTO `consignors` VALUES ('8', 'SUP-C-002', 'SUPER WHEELS AUTOMOBILES PVT LTD  ', 'MANDHANA KANPUR NAGAR UTTAR PRADESH - ', '627', '7275954009', 'moveup19kanpur@gmail.com', '09ABBCS3457N1ZT', '2026-03-18 16:35:10');
INSERT INTO `consignors` VALUES ('9', 'SUP-C-003', 'bajaj super wheels automobile pvt ltd ', 'Lakda purwa tindwari road banda', '803', '9935607191', 'abhi.kanpur07@gmail.com', '09ABBCS3457N1ZT', '2026-03-19 16:23:52');
INSERT INTO `consignors` VALUES ('10', 'GLO-C-001', 'Safetix proactive ', '1002-1002 site-3 UPSIDC INDS.AREA AKRAMPUR UNNAO', '805', '', '', '09AAECM2826P1ZZ', '2026-04-09 05:40:06');
INSERT INTO `consignors` VALUES ('11', 'SOU-C-003', 'SOURCE INDIA SHOES', 'Head Office-124/B/119 Govind Nagar,, Kanpur(U.P) Pin-208006
Place of Dispatch - JAJMAU KANPUR -208010
', '802', '09990848912', 'info.gs30@gmail.com', '09ALNPM6501A1ZB', '2026-04-09 17:00:22');
INSERT INTO `consignors` VALUES ('12', 'MAS-C-002', 'MR. SHAQIB KHAN', '87/73-B HINDUSTAN TANNERY COMPOUND , JAJMAU KANPUR-208011', '802', '8887664072', 'info@mashgroup.in', '09AABFM4240M1Z6', '2026-04-19 14:16:37');
INSERT INTO `consignors` VALUES ('13', 'SOU-C-004', 'SOURCE INDIA SHOES ', 'JAJMAU KANPUR', '627', '9990848912', 'info.gs30@gmail.com', '09ALNPM6501A1ZB', '2026-05-20 05:01:59');

DROP TABLE IF EXISTS `vehicle_types`;
CREATE TABLE `vehicle_types` (
    id INTEGER,
    name TEXT NOT NULL,
    capacity TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `vehicle_types` VALUES ('1', '22FT', '6500', '2026-02-24 17:16:01');
INSERT INTO `vehicle_types` VALUES ('2', '32 ft SXl container ', '9 ton', '2026-02-28 07:08:31');
INSERT INTO `vehicle_types` VALUES ('3', 'BOLERO PICKUP', '1200 KG ', '2026-03-09 09:11:21');
INSERT INTO `vehicle_types` VALUES ('4', '14 ft vehicle ', '4 ton', '2026-03-13 06:46:44');
INSERT INTO `vehicle_types` VALUES ('5', 'TATA ACE', '750 KG', '2026-04-19 14:23:30');

DROP TABLE IF EXISTS `manifests`;
CREATE TABLE `manifests` (
    id INTEGER,
    manifest_no VARCHAR(255) NOT NULL,
    date DATETIME NOT NULL,
    origin VARCHAR(255) NOT NULL,
    destination VARCHAR(255) NOT NULL,
    vendor_id INT,
    vehicle_no VARCHAR(255),
    driver_name VARCHAR(255),
    driver_phone VARCHAR(255),
    total_weight VARCHAR(255) DEFAULT 0,
    total_packages INT DEFAULT 0,
    hire_amount VARCHAR(255) DEFAULT 0,
    advance_amount VARCHAR(255) DEFAULT 0,
    balance_amount VARCHAR(255) DEFAULT 0,
    remarks TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    branch_id INTEGER DEFAULT 1,
    created_by INTEGER DEFAULT 1,
    status VARCHAR(50) DEFAULT 'In Transit',
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `manifest_details`;
CREATE TABLE `manifest_details` (
    id INTEGER,
    manifest_id INT NOT NULL,
    docket_id INT NOT NULL,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `journal_entries`;
CREATE TABLE `journal_entries` (
    id INTEGER,
    entry_date DATETIME NOT NULL,
    entry_no VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(255) DEFAULT 'Journal',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    payment_date DATETIME,
    utr_no VARCHAR(255),
    branch_id INTEGER DEFAULT 1,
    created_by INTEGER DEFAULT 1,
    reference_no VARCHAR(255) DEFAULT NULL,
    narration TEXT DEFAULT NULL,
    PRIMARY KEY (id)
);

INSERT INTO `journal_entries` VALUES ('2', '2026-02-28', 'INV/25-26/00046', 'Sales Bill Generated for Sample Customer 5', 'Sales', '2026-02-28 18:28:50', NULL, NULL, '1', '2', NULL, NULL);
INSERT INTO `journal_entries` VALUES ('3', '2026-04-19', 'VB/CNB/26-27/00001', 'Vendor Bill for 1 Dockets', 'Purchase', '2026-04-19 15:32:06', NULL, NULL, '1', '1', NULL, NULL);
INSERT INTO `journal_entries` VALUES ('4', '2026-04-19', 'VB/CNB/26-27/00002', 'Vendor Bill for 6 Dockets', 'Purchase', '2026-04-19 15:40:08', NULL, NULL, '1', '2', NULL, NULL);
INSERT INTO `journal_entries` VALUES ('5', '2026-04-19', 'VP/26-27/00001', 'Vendor Advance Payment - 1 Dockets', 'Payment', '2026-04-19 15:41:36', NULL, NULL, '1', '1', NULL, NULL);
INSERT INTO `journal_entries` VALUES ('6', '2026-04-19', 'JRN/CNB/26-27/00001', '', 'Journal', '2026-04-19 16:02:48', NULL, NULL, '1', '2', NULL, NULL);
INSERT INTO `journal_entries` VALUES ('7', '2026-05-10', 'CON/26-27/00001', '', 'Contra', '2026-05-10 08:04:02', '2026-05-10', NULL, '1', '1', NULL, NULL);
INSERT INTO `journal_entries` VALUES ('8', '2026-05-10', 'VP/26-27/00002', 'Vendor Payment - 1 Dockets', 'Payment', '2026-05-10 10:15:36', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `journal_entries` VALUES ('9', '2026-05-20', 'VP/26-27/00003', 'Vendor Advance Payment - 1 Dockets', 'Payment', '2026-05-20 05:07:31', NULL, NULL, '1', '1', NULL, NULL);

DROP TABLE IF EXISTS `journal_items`;
CREATE TABLE `journal_items` (
    id INTEGER,
    journal_id INT NOT NULL,
    ledger_id INT NOT NULL,
    debit VARCHAR(255) DEFAULT 0.00,
    credit VARCHAR(255) DEFAULT 0.00,
    PRIMARY KEY (id)
);

INSERT INTO `journal_items` VALUES ('1', '2', '105', '52000', '0.0');
INSERT INTO `journal_items` VALUES ('2', '3', '20', '50000', '0.0');
INSERT INTO `journal_items` VALUES ('3', '3', '6', '0.0', '50000');
INSERT INTO `journal_items` VALUES ('4', '4', '20', '61000', '0.0');
INSERT INTO `journal_items` VALUES ('5', '4', '10', '0.0', '61000');
INSERT INTO `journal_items` VALUES ('6', '5', '12', '2000', '0.0');
INSERT INTO `journal_items` VALUES ('7', '5', '22', '0.0', '20');
INSERT INTO `journal_items` VALUES ('8', '5', '21', '0.0', '1980');
INSERT INTO `journal_items` VALUES ('9', '6', '15', '5000', '0');
INSERT INTO `journal_items` VALUES ('10', '6', '20', '0', '5000');
INSERT INTO `journal_items` VALUES ('11', '7', '16', '5000', '0');
INSERT INTO `journal_items` VALUES ('12', '7', '7', '0', '5000');
INSERT INTO `journal_items` VALUES ('13', '8', '10', '5000', '0.0');
INSERT INTO `journal_items` VALUES ('14', '8', '21', '0.0', '4900');
INSERT INTO `journal_items` VALUES ('15', '8', '22', '0.0', '100');
INSERT INTO `journal_items` VALUES ('16', '9', '10', '2500', '0.0');
INSERT INTO `journal_items` VALUES ('17', '9', '21', '0.0', '2500');

DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` (
    id INTEGER,
    purchase_date DATETIME NOT NULL,
    vendor_id INT NOT NULL,
    invoice_no VARCHAR(255) NOT NULL,
    hsn_sac VARCHAR(255),
    taxable_amount VARCHAR(255) DEFAULT 0.00,
    gst_rate VARCHAR(255) DEFAULT 0.00,
    gst_amount VARCHAR(255) DEFAULT 0.00,
    total_amount VARCHAR(255) DEFAULT 0.00,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    product_id INT,
    quantity VARCHAR(255) DEFAULT 0,
    branch_id INTEGER DEFAULT 1,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `vendor_bills`;
CREATE TABLE `vendor_bills` (
    id INTEGER,
    bill_no VARCHAR(255) NOT NULL,
    bill_date DATETIME NOT NULL,
    vendor_id INT NOT NULL,
    docket_id INT,
    contract_amount VARCHAR(255) DEFAULT 0.00,
    loading_charges VARCHAR(255) DEFAULT 0.00,
    detention_charges VARCHAR(255) DEFAULT 0.00,
    mamul_deduction VARCHAR(255) DEFAULT 0.00,
    other_deduction VARCHAR(255) DEFAULT 0.00,
    total_amount VARCHAR(255) DEFAULT 0.00,
    status VARCHAR(255) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    advance_amount VARCHAR(255) DEFAULT 0.00,
    net_amount VARCHAR(255) DEFAULT 0.00,
    gst_amount VARCHAR(255) DEFAULT 0,
    manifest_id INT,
    branch_id INTEGER DEFAULT 1,
    vendor_invoice_no VARCHAR(255) DEFAULT NULL,
    vendor_invoice_date DATE DEFAULT NULL,
    gst_rate DECIMAL(5,2) DEFAULT 0.00,
    PRIMARY KEY (id)
);

INSERT INTO `vendor_bills` VALUES ('1', 'VB/CNB/26-27/00001', '2026-04-19', '6', '6', '50000', '0', '0', '0', '0', '50000', 'Pending', '2026-04-19 15:32:06', '0.0', '0.0', '0', NULL, '1', '', '', '0');
INSERT INTO `vendor_bills` VALUES ('2', 'VB/CNB/26-27/00002', '2026-04-19', '10', '25', '5000', '0', '0', '0', '0', '5000', 'Pending', '2026-04-19 15:40:08', '0.0', '0.0', '0', NULL, '1', '', '', '');
INSERT INTO `vendor_bills` VALUES ('3', 'VB/CNB/26-27/00002', '2026-04-19', '10', '24', '36000', '0', '0', '0', '0', '36000', 'Pending', '2026-04-19 15:40:08', '0.0', '0.0', '0', NULL, '1', '', '', '');
INSERT INTO `vendor_bills` VALUES ('4', 'VB/CNB/26-27/00002', '2026-04-19', '10', '19', '0', '0', '0', '0', '0', '0', 'Pending', '2026-04-19 15:40:08', '0.0', '0.0', '0', NULL, '1', '', '', '');
INSERT INTO `vendor_bills` VALUES ('5', 'VB/CNB/26-27/00002', '2026-04-19', '10', '16', '0', '0', '0', '0', '0', '0', 'Pending', '2026-04-19 15:40:08', '0.0', '0.0', '0', NULL, '1', '', '', '');
INSERT INTO `vendor_bills` VALUES ('6', 'VB/CNB/26-27/00002', '2026-04-19', '10', '8', '7000', '0', '0', '0', '0', '7000', 'Pending', '2026-04-19 15:40:08', '0.0', '0.0', '0', NULL, '1', '', '', '');
INSERT INTO `vendor_bills` VALUES ('7', 'VB/CNB/26-27/00002', '2026-04-19', '10', '7', '13000', '0', '0', '0', '0', '13000', 'Pending', '2026-04-19 15:40:08', '0.0', '0.0', '0', NULL, '1', '', '', '');

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
    id INTEGER,
    name TEXT NOT NULL,
    hsn_code TEXT,
    unit TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
    id INTEGER,
    expense_date DATETIME NOT NULL,
    expense_type VARCHAR(255),
    amount VARCHAR(255),
    vendor_id INT,
    gst_rate VARCHAR(255) DEFAULT 0,
    gst_amount VARCHAR(255) DEFAULT 0,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `customer_rates`;
CREATE TABLE `customer_rates` (
    id INTEGER,
    customer_id INT,
    origin VARCHAR(255),
    destination VARCHAR(255),
    vehicle_type VARCHAR(255),
    rate_type VARCHAR(255),
    rate VARCHAR(255),
    docket_charges VARCHAR(255) DEFAULT 0,
    fuel_charges VARCHAR(255) DEFAULT 0,
    pickup_delivery_charges VARCHAR(255) DEFAULT 0,
    oda_charges VARCHAR(255) DEFAULT 0,
    hamali_charges VARCHAR(255) DEFAULT 0,
    detention_charges VARCHAR(255) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `user_permissions`;
CREATE TABLE `user_permissions` (
    id INTEGER,
    user_id INT NOT NULL,
    permission_key VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO `user_permissions` VALUES ('420', '1', 'dashboard');
INSERT INTO `user_permissions` VALUES ('421', '1', 'switch_branch');
INSERT INTO `user_permissions` VALUES ('422', '1', 'master_company');
INSERT INTO `user_permissions` VALUES ('423', '1', 'master_branch');
INSERT INTO `user_permissions` VALUES ('424', '1', 'master_branch_assign');
INSERT INTO `user_permissions` VALUES ('425', '1', 'master_user');
INSERT INTO `user_permissions` VALUES ('426', '1', 'master_state');
INSERT INTO `user_permissions` VALUES ('427', '1', 'master_city');
INSERT INTO `user_permissions` VALUES ('428', '1', 'master_group');
INSERT INTO `user_permissions` VALUES ('429', '1', 'master_ledger');
INSERT INTO `user_permissions` VALUES ('430', '1', 'master_customer');
INSERT INTO `user_permissions` VALUES ('431', '1', 'master_vendor');
INSERT INTO `user_permissions` VALUES ('432', '1', 'master_consignor');
INSERT INTO `user_permissions` VALUES ('433', '1', 'master_consignee');
INSERT INTO `user_permissions` VALUES ('434', '1', 'master_bank');
INSERT INTO `user_permissions` VALUES ('435', '1', 'master_vehicle');
INSERT INTO `user_permissions` VALUES ('436', '1', 'master_rate');
INSERT INTO `user_permissions` VALUES ('437', '1', 'master_product');
INSERT INTO `user_permissions` VALUES ('438', '1', 'master_settings');
INSERT INTO `user_permissions` VALUES ('439', '1', 'master_edit');
INSERT INTO `user_permissions` VALUES ('440', '1', 'master_delete');
INSERT INTO `user_permissions` VALUES ('441', '1', 'op_docket_entry');
INSERT INTO `user_permissions` VALUES ('442', '1', 'op_docket_list');
INSERT INTO `user_permissions` VALUES ('443', '1', 'op_manifest');
INSERT INTO `user_permissions` VALUES ('444', '1', 'op_thc');
INSERT INTO `user_permissions` VALUES ('445', '1', 'op_thc_arrival');
INSERT INTO `user_permissions` VALUES ('446', '1', 'op_stock_update');
INSERT INTO `user_permissions` VALUES ('447', '1', 'op_drs');
INSERT INTO `user_permissions` VALUES ('448', '1', 'op_tracking');
INSERT INTO `user_permissions` VALUES ('449', '1', 'op_pod');
INSERT INTO `user_permissions` VALUES ('450', '1', 'op_upload_masters');
INSERT INTO `user_permissions` VALUES ('451', '1', 'op_backup');
INSERT INTO `user_permissions` VALUES ('452', '1', 'op_upload_dockets');
INSERT INTO `user_permissions` VALUES ('453', '1', 'op_edit');
INSERT INTO `user_permissions` VALUES ('454', '1', 'op_delete');
INSERT INTO `user_permissions` VALUES ('455', '1', 'acc_purchase');
INSERT INTO `user_permissions` VALUES ('456', '1', 'acc_vendor_bill');
INSERT INTO `user_permissions` VALUES ('457', '1', 'acc_vendor_advance');
INSERT INTO `user_permissions` VALUES ('458', '1', 'acc_vendor_payment');
INSERT INTO `user_permissions` VALUES ('459', '1', 'acc_expense');
INSERT INTO `user_permissions` VALUES ('460', '1', 'acc_journal');
INSERT INTO `user_permissions` VALUES ('461', '1', 'acc_contra');
INSERT INTO `user_permissions` VALUES ('462', '1', 'acc_bank_rec');
INSERT INTO `user_permissions` VALUES ('463', '1', 'acc_ledger_view');
INSERT INTO `user_permissions` VALUES ('464', '1', 'acc_billing');
INSERT INTO `user_permissions` VALUES ('465', '1', 'acc_collection');
INSERT INTO `user_permissions` VALUES ('466', '1', 'acc_edit');
INSERT INTO `user_permissions` VALUES ('467', '1', 'acc_delete');
INSERT INTO `user_permissions` VALUES ('468', '1', 'rpt_docket');
INSERT INTO `user_permissions` VALUES ('469', '1', 'rpt_unbilled');
INSERT INTO `user_permissions` VALUES ('470', '1', 'rpt_vendor');
INSERT INTO `user_permissions` VALUES ('471', '1', 'rpt_customer');
INSERT INTO `user_permissions` VALUES ('472', '1', 'rpt_gst');
INSERT INTO `user_permissions` VALUES ('473', '1', 'rpt_tds');
INSERT INTO `user_permissions` VALUES ('474', '1', 'rpt_pl');
INSERT INTO `user_permissions` VALUES ('475', '1', 'rpt_bs');
INSERT INTO `user_permissions` VALUES ('476', '1', 'rpt_trial');
INSERT INTO `user_permissions` VALUES ('477', '1', 'rpt_booking');
INSERT INTO `user_permissions` VALUES ('478', '1', 'rpt_gstr1');
INSERT INTO `user_permissions` VALUES ('479', '1', 'rpt_gstr2b');
INSERT INTO `user_permissions` VALUES ('480', '1', 'rpt_gstr3b');
INSERT INTO `user_permissions` VALUES ('481', '1', 'rpt_stock');
INSERT INTO `user_permissions` VALUES ('482', '1', 'rpt_payments');

DROP TABLE IF EXISTS `user_settings`;
CREATE TABLE `user_settings` (
    user_id INTEGER,
    theme VARCHAR(255) DEFAULT 'light',
    notifications_enabled VARCHAR(255) DEFAULT 1,
    language VARCHAR(255) DEFAULT 'en',
    font_family TEXT DEFAULT 'Roboto',
    font_size VARCHAR(20) DEFAULT '14px',
    letter_spacing VARCHAR(20) DEFAULT 'normal',
    input_bg_color VARCHAR(20) DEFAULT '#ffffff',
    input_text_color VARCHAR(20) DEFAULT '#212529',
    input_border_color VARCHAR(20) DEFAULT '#ced4da',
    input_focus_color VARCHAR(20) DEFAULT '#86b7fe',
    input_border_radius VARCHAR(20) DEFAULT '0.25rem',
    label_color VARCHAR(20) DEFAULT '#212529',
    header_bg_color VARCHAR(20) DEFAULT '#ffffff',
    header_text_color VARCHAR(20) DEFAULT '#212529',
    card_header_bg_color VARCHAR(20) DEFAULT '#ffffff',
    card_header_text_color VARCHAR(20) DEFAULT '#1e293b',
    table_header_bg_color VARCHAR(20) DEFAULT '#343a40',
    table_header_text_color VARCHAR(20) DEFAULT '#ffffff',
    PRIMARY KEY (user_id)
);

INSERT INTO `user_settings` VALUES ('1', 'new', '0', 'en', 'Lato', '16px', '0.5px', '#ffffff', '#212529', '#2e6194', '#396fc0', '30px', '#05121f', '#b45f5f', '#212529', '#f23a3a', '#1e293b', '#343a40', '#a14545');
INSERT INTO `user_settings` VALUES ('2', 'ocean_blue', '1', 'en', 'Quicksand', '18px', '2px', '#f9f5f5', '#040118', '#090c62', '#c5637b', '10px', '#212529', '#104ad1', '#010a13', '#d6e3e6', '#1451b3', '#2c4763', '#ffffff');

DROP TABLE IF EXISTS `thc`;
CREATE TABLE `thc` (
    id INTEGER,
    thc_no VARCHAR(255) NOT NULL,
    date DATETIME NOT NULL,
    branch_id INT,
    vendor_id INT,
    vehicle_no VARCHAR(255),
    driver_name VARCHAR(255),
    driver_phone VARCHAR(255),
    hire_amount VARCHAR(255) DEFAULT 0,
    advance_amount VARCHAR(255) DEFAULT 0,
    balance_amount VARCHAR(255) DEFAULT 0,
    remarks TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'In Transit',
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `thc_details`;
CREATE TABLE `thc_details` (
    id INTEGER,
    thc_id INT NOT NULL,
    manifest_id INT NOT NULL,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `drs`;
CREATE TABLE `drs` (
    id INTEGER,
    drs_no VARCHAR(255) NOT NULL,
    date DATETIME NOT NULL,
    branch_id INT,
    vehicle_no VARCHAR(255),
    driver_name VARCHAR(255),
    delivery_boy VARCHAR(255),
    remarks TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `drs_details`;
CREATE TABLE `drs_details` (
    id INTEGER,
    drs_id INT NOT NULL,
    docket_id INT NOT NULL,
    status VARCHAR(255) DEFAULT 'Out for Delivery',
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `stock_updates`;
CREATE TABLE `stock_updates` (
    id INTEGER,
    docket_id INT NOT NULL,
    prev_status VARCHAR(255),
    new_status VARCHAR(255),
    updated_by INT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `stock_updates` VALUES ('1', '9', NULL, 'In Stock', '2', '2026-03-15 07:28:14');

DROP TABLE IF EXISTS `document_series`;
CREATE TABLE `document_series` (
    id INTEGER,
    document_type VARCHAR(255) NOT NULL,
    prefix VARCHAR(255) NOT NULL,
    start_number INT DEFAULT 1,
    end_number INT DEFAULT 999999,
    current_number INT DEFAULT 0,
    is_active VARCHAR(255) DEFAULT 1,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `document_series` VALUES ('1', 'docket', '', '1', '999999', '0', '1', '2026-02-25 16:16:11');
INSERT INTO `document_series` VALUES ('2', 'customer_bill', 'INV', '1', '999999', '45', '1', '2026-02-25 16:16:11');
INSERT INTO `document_series` VALUES ('3', 'vendor_bill', 'VB', '1', '999999', '0', '1', '2026-02-25 16:16:11');
INSERT INTO `document_series` VALUES ('4', 'vendor_payment', 'VP', '1', '999999', '0', '1', '2026-02-25 16:16:12');
INSERT INTO `document_series` VALUES ('5', 'expense_payment', 'EXP', '1', '999999', '0', '1', '2026-02-25 16:16:12');
INSERT INTO `document_series` VALUES ('6', 'collection', 'RCP', '1', '999999', '0', '1', '2026-02-25 16:16:12');
INSERT INTO `document_series` VALUES ('7', 'drs', 'DRS', '1', '999999', '0', '1', '2026-02-25 16:16:13');
INSERT INTO `document_series` VALUES ('8', 'manifest', 'MF', '1', '999999', '0', '1', '2026-02-25 16:16:13');
INSERT INTO `document_series` VALUES ('9', 'thc', 'THC', '1', '999999', '0', '1', '2026-02-25 16:16:13');
INSERT INTO `document_series` VALUES ('10', 'customer_challan', 'CHL', '1', '999999', '0', '1', '2026-02-25 16:21:49');
INSERT INTO `document_series` VALUES ('11', 'fleet_bill', 'FB', '1', '999999', '0', '1', '2026-02-25 16:21:49');
INSERT INTO `document_series` VALUES ('12', 'fleet_trip', 'TRIP', '1', '999999', '0', '1', '2026-02-25 16:21:49');

DROP TABLE IF EXISTS `customer_challans`;
CREATE TABLE `customer_challans` (
    id INTEGER,
    challan_no VARCHAR(255) NOT NULL,
    date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    manifest_id INT DEFAULT NULL,
    origin VARCHAR(255) DEFAULT NULL,
    destination VARCHAR(255) DEFAULT NULL,
    vehicle_no VARCHAR(255) DEFAULT NULL,
    vehicle_type VARCHAR(255) DEFAULT NULL,
    total_weight VARCHAR(255) DEFAULT 0.00,
    freight_total VARCHAR(255) DEFAULT 0.00,
    advance_amount VARCHAR(255) DEFAULT 0.00,
    balance_amount VARCHAR(255) DEFAULT 0.00,
    bank_id INT DEFAULT NULL,
    terms TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    docket_id INT DEFAULT NULL,
    branch_id INTEGER DEFAULT 1,
    PRIMARY KEY (id)
);

INSERT INTO `customer_challans` VALUES ('2', 'CHL/CNB/26-27/00001', '2026-05-20', '13', NULL, 'Kanpur', 'Singrauli', 'UP77AT5631', 'BOLERO PICKUP', '950', '18000', '18000', '0', '1', '1. Payment advance within 24 hours.
2. Detention charges 2000 / day.
3. Unloading charges paid by customer.', '2026-05-20 05:42:05', '7', '1');

DROP TABLE IF EXISTS `vendor_payments`;
CREATE TABLE `vendor_payments` (
    id INTEGER,
    vendor_id INT NOT NULL,
    docket_id INT NOT NULL,
    payment_date DATETIME NOT NULL,
    voucher_no VARCHAR(255) NOT NULL,
    paid_amount VARCHAR(255) DEFAULT 0.00,
    tds_amount VARCHAR(255) DEFAULT 0.00,
    bank_id INT,
    utr_no VARCHAR(255),
    narration TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    clear_date DATE DEFAULT NULL,
    tds_challan_id INTEGER,
    branch_id INTEGER DEFAULT 1,
    surcharge DECIMAL(10,2) DEFAULT 0.00,
    cess DECIMAL(10,2) DEFAULT 0.00,
    contract_amount DECIMAL(15,2) DEFAULT 0.00,
    deduction_amount DECIMAL(15,2) DEFAULT 0.00,
    PRIMARY KEY (id)
);

INSERT INTO `vendor_payments` VALUES ('1', '12', '20', '2026-04-19', 'VP/26-27/00001', '1980', '20', NULL, '', '', '2026-04-19 15:41:36', NULL, NULL, '1', '0', '0', '2000', '0');
INSERT INTO `vendor_payments` VALUES ('2', '10', '25', '2026-05-10', 'VP/26-27/00002', '4900', '100.00', NULL, '', NULL, '2026-05-10 10:15:36', NULL, NULL, '1', '0', '0', '5000', '0');
INSERT INTO `vendor_payments` VALUES ('3', '10', '26', '2026-05-20', 'VP/26-27/00003', '2500', '0', NULL, '', NULL, '2026-05-20 05:07:31', NULL, NULL, '1', '0', '0', '2500', '0');

DROP TABLE IF EXISTS `tds_challans`;
CREATE TABLE `tds_challans` (
    id INTEGER,
    month TEXT,
    section_code TEXT,
    tds_rate REAL,
    challan_no TEXT,
    bsr_code TEXT,
    cin TEXT,
    bank_ref_no TEXT,
    row_count INTEGER,
    amount REAL,
    interest REAL DEFAULT 0,
    penalty REAL DEFAULT 0,
    total REAL,
    challan_date DATE,
    head TEXT,
    financial_year TEXT,
    quarter TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `vehicles`;
CREATE TABLE `vehicles` (
    id INTEGER,
    registration_number TEXT NOT NULL,
    vehicle_code TEXT NOT NULL,
    vehicle_type_id INTEGER,
    make TEXT,
    model TEXT,
    manufacturing_year INTEGER,
    vin_number TEXT,
    engine_number TEXT,
    chassis_number TEXT,
    owner_name TEXT,
    ownership_type TEXT DEFAULT 'Company Owned',
    purchase_date DATE,
    purchase_amount REAL DEFAULT 0,
    fuel_type TEXT DEFAULT 'Diesel',
    fuel_capacity REAL DEFAULT 0,
    mileage_expected REAL DEFAULT 0,
    current_odometer REAL DEFAULT 0,
    payload_capacity REAL DEFAULT 0,
    status TEXT DEFAULT 'Active',
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    current_driver_id INTEGER DEFAULT NULL,
    rc_no VARCHAR(50),
    rc_expiry DATE,
    insurance_expiry DATE,
    permit_expiry DATE,
    vehicle_no VARCHAR(50),
    PRIMARY KEY (id)
);

INSERT INTO `vehicles` VALUES ('1', 'HP93A8931', 'HP93A8931', '1', NULL, 'LPT 1613', NULL, NULL, NULL, NULL, NULL, 'Company Owned', NULL, '0', 'Diesel', '0', '0', '0', '0', 'Active', NULL, '2026-02-24 17:17:36', '2026-02-24 17:17:36', '1', '5544555', '2026-03-07', '2026-03-08', '2026-03-07', 'HP93A8931');
INSERT INTO `vehicles` VALUES ('2', 'MH12AB1001-688', 'MH12AB1001-688', '1', NULL, 'Tata Ace', NULL, NULL, NULL, NULL, NULL, 'Company Owned', NULL, '0', 'Diesel', '0', '0', '30350', '0', 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL, NULL, NULL, NULL, NULL, 'MH12AB1001-688');
INSERT INTO `vehicles` VALUES ('3', 'MH12AB1002-404', 'MH12AB1002-404', '1', NULL, 'Eicher Pro', NULL, NULL, NULL, NULL, NULL, 'Company Owned', NULL, '0', 'Diesel', '0', '0', '18679', '0', 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL, NULL, NULL, NULL, NULL, 'MH12AB1002-404');
INSERT INTO `vehicles` VALUES ('4', 'MH12AB1003-688', 'MH12AB1003-688', '2', NULL, 'Mahindra Bolero', NULL, NULL, NULL, NULL, NULL, 'Company Owned', NULL, '0', 'Diesel', '0', '0', '43856', '0', 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL, NULL, NULL, NULL, NULL, 'MH12AB1003-688');
INSERT INTO `vehicles` VALUES ('5', 'MH12AB1004-438', 'MH12AB1004-438', '2', NULL, 'Ashok Leyland', NULL, NULL, NULL, NULL, NULL, 'Company Owned', NULL, '0', 'Diesel', '0', '0', '10149', '0', 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL, NULL, NULL, NULL, NULL, 'MH12AB1004-438');

DROP TABLE IF EXISTS `vehicle_documents`;
CREATE TABLE `vehicle_documents` (
    id INTEGER,
    vehicle_id INTEGER NOT NULL,
    document_type TEXT NOT NULL,
    document_number TEXT,
    expiry_date DATE,
    alert_days INTEGER DEFAULT 30,
    file_path TEXT,
    status TEXT DEFAULT 'Active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_current INTEGER DEFAULT 1,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `drivers`;
CREATE TABLE `drivers` (
    id INTEGER,
    driver_code TEXT NOT NULL,
    full_name TEXT NOT NULL,
    date_of_birth DATE,
    gender TEXT,
    blood_group TEXT,
    marital_status TEXT DEFAULT 'Single',
    license_number TEXT NOT NULL,
    license_type TEXT,
    license_issue_date DATE,
    license_expiry_date DATE,
    issuing_authority TEXT,
    phone_primary TEXT,
    phone_secondary TEXT,
    email TEXT,
    joining_date DATE,
    emergency_contact_name TEXT,
    emergency_contact_phone TEXT,
    emergency_contact_relationship TEXT,
    permanent_address TEXT,
    current_address TEXT,
    city TEXT,
    state TEXT,
    pincode TEXT,
    status TEXT DEFAULT 'Active',
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    current_vehicle_id INTEGER DEFAULT NULL,
    PRIMARY KEY (id)
);

INSERT INTO `drivers` VALUES ('1', 'DRV1771954530', 'KAMAL KUMAR MISHRA', NULL, NULL, NULL, 'Single', 'XXX0000011', NULL, NULL, '2026-03-08', NULL, '07695065498', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '730
eros corporate park', NULL, NULL, NULL, 'Active', NULL, '2026-02-24 17:35:30', '2026-02-24 17:35:30', '1');
INSERT INTO `drivers` VALUES ('2', 'DRV1771955656830', 'Ramesh 66', NULL, NULL, NULL, 'Single', 'LIC373466', NULL, NULL, NULL, NULL, '9384087203', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL);
INSERT INTO `drivers` VALUES ('3', 'DRV1771955656648', 'Suresh 40', NULL, NULL, NULL, 'Single', 'LIC583099', NULL, NULL, NULL, NULL, '9478027913', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL);
INSERT INTO `drivers` VALUES ('4', 'DRV1771955656404', 'Mahesh 36', NULL, NULL, NULL, 'Single', 'LIC838452', NULL, NULL, NULL, NULL, '9381241088', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL);
INSERT INTO `drivers` VALUES ('5', 'DRV1771955656412', 'Dinesh 11', NULL, NULL, NULL, 'Single', 'LIC224380', NULL, NULL, NULL, NULL, '9418497387', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Active', NULL, '2026-02-24 17:54:16', '2026-02-24 17:54:16', NULL);

DROP TABLE IF EXISTS `driver_documents`;
CREATE TABLE `driver_documents` (
    id INTEGER,
    driver_id INTEGER NOT NULL,
    document_type TEXT NOT NULL,
    document_name TEXT,
    file_name TEXT,
    file_path TEXT,
    file_size INTEGER,
    file_type TEXT,
    mime_type TEXT,
    document_number TEXT,
    issue_date DATE,
    expiry_date DATE,
    issuing_authority TEXT,
    status TEXT DEFAULT 'Verified',
    uploaded_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_current INTEGER DEFAULT 1,
    verification_date DATE,
    verified_by INTEGER,
    rejection_reason TEXT,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `driver_route_expenses`;
CREATE TABLE `driver_route_expenses` (
    id INTEGER,
    driver_id INTEGER NOT NULL,
    vehicle_id INTEGER,
    trip_id INTEGER,
    expense_date DATE NOT NULL,
    fuel_expense REAL DEFAULT 0,
    toll_expense REAL DEFAULT 0,
    accommodation_expense REAL DEFAULT 0,
    food_expense REAL DEFAULT 0,
    other_expense REAL DEFAULT 0,
    fuel_quantity REAL,
    fuel_price_per_liter REAL,
    fuel_station_name TEXT,
    fuel_station_location TEXT,
    odometer_reading_start REAL,
    odometer_reading_end REAL,
    toll_plaza_name TEXT,
    toll_amount REAL,
    toll_receipt_number TEXT,
    start_location TEXT,
    end_location TEXT,
    gps_coordinates TEXT,
    payment_method TEXT DEFAULT 'Cash',
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_expense REAL DEFAULT 0,
    status TEXT DEFAULT 'Submitted',
    submitted_by INTEGER,
    submitted_at DATETIME,
    approved_by INTEGER,
    approved_at DATETIME,
    rejection_reason TEXT,
    payment_reference TEXT,
    payment_date DATE,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `vehicle_maintenance`;
CREATE TABLE `vehicle_maintenance` (
    id INTEGER,
    vehicle_id INTEGER NOT NULL,
    maintenance_type TEXT,
    description TEXT,
    service_date DATE,
    cost REAL DEFAULT 0,
    odometer_reading REAL,
    status TEXT DEFAULT 'Completed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `driver_expense_receipts`;
CREATE TABLE `driver_expense_receipts` (
    id INTEGER,
    expense_id INTEGER NOT NULL,
    receipt_file TEXT NOT NULL,
    description TEXT,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    receipt_type TEXT,
    receipt_number TEXT,
    receipt_date DATE,
    amount REAL,
    file_name TEXT,
    file_path TEXT,
    file_size INTEGER,
    file_type TEXT,
    uploaded_by INTEGER,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `driver_contacts`;
CREATE TABLE `driver_contacts` (
    id INTEGER,
    driver_id INTEGER NOT NULL,
    contact_type TEXT NOT NULL,
    phone_number TEXT,
    email TEXT,
    is_primary INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `fixed_assets`;
CREATE TABLE `fixed_assets` (
    id INTEGER,
    name TEXT NOT NULL,
    code TEXT,
    purchase_date DATE,
    cost DECIMAL(15,2) DEFAULT 0.00,
    salvage_value DECIMAL(15,2) DEFAULT 0.00,
    useful_life_years DECIMAL(10,2),
    depreciation_method TEXT,
    depreciation_rate DECIMAL(5,2),
    asset_ledger_id INTEGER,
    accumulated_dep_ledger_id INTEGER,
    dep_expense_ledger_id INTEGER,
    status TEXT DEFAULT 'Active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `bank_loans`;
CREATE TABLE `bank_loans` (
    id INTEGER,
    bank_name TEXT NOT NULL,
    loan_account_number TEXT,
    principal_amount DECIMAL(15,2) DEFAULT 0.00,
    interest_rate DECIMAL(5,2) DEFAULT 0.00,
    start_date DATE,
    end_date DATE,
    term_months INTEGER,
    loan_ledger_id INTEGER,
    interest_ledger_id INTEGER,
    status TEXT DEFAULT 'Active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `loan_repayments`;
CREATE TABLE `loan_repayments` (
    id INTEGER,
    loan_id INTEGER NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(15,2) DEFAULT 0.00,
    principal_component DECIMAL(15,2) DEFAULT 0.00,
    interest_component DECIMAL(15,2) DEFAULT 0.00,
    reference_no TEXT,
    journal_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ledger_id INTEGER DEFAULT NULL,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `depreciation_schedules`;
CREATE TABLE `depreciation_schedules` (
    id INTEGER,
    asset_id INTEGER NOT NULL,
    fy_start DATE,
    fy_end DATE,
    amount DECIMAL(15,2) DEFAULT 0.00,
    accumulated_amount DECIMAL(15,2) DEFAULT 0.00,
    book_value DECIMAL(15,2) DEFAULT 0.00,
    is_posted BOOLEAN DEFAULT 0,
    journal_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `user_branches`;
CREATE TABLE `user_branches` (
    id INTEGER,
    user_id INTEGER NOT NULL,
    branch_id INTEGER NOT NULL,
    is_default BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `driver_payments`;
CREATE TABLE `driver_payments` (
    id INTEGER,
    docket_id INTEGER NOT NULL,
    voucher_no VARCHAR(50) NOT NULL,
    payment_date DATE NOT NULL,
    category VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    liters DECIMAL(10,2) DEFAULT NULL,
    rate DECIMAL(10,2) DEFAULT NULL,
    payment_mode VARCHAR(50) NOT NULL,
    bank_id INTEGER DEFAULT NULL,
    utr_no VARCHAR(50) DEFAULT NULL,
    utr_date DATE DEFAULT NULL,
    remarks TEXT,
    branch_id INTEGER DEFAULT 1,
    created_by INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `driver_payments` VALUES ('1', '3', 'VOU-1771955656622', '2026-02-24', 'Trip Expense', '881', NULL, NULL, 'Cash', NULL, NULL, NULL, 'Dummy Payment', '1', '1', '2026-02-24 17:54:16');
INSERT INTO `driver_payments` VALUES ('2', '2', 'VOU-1771955656483', '2026-02-24', 'Urea', '266', NULL, NULL, 'Cash', NULL, NULL, NULL, 'Dummy Payment', '1', '1', '2026-02-24 17:54:16');
INSERT INTO `driver_payments` VALUES ('3', '3', 'VOU-1771955656854', '2026-02-24', 'Urea', '211', NULL, NULL, 'Cash', NULL, NULL, NULL, 'Dummy Payment', '1', '1', '2026-02-24 17:54:16');
INSERT INTO `driver_payments` VALUES ('4', '3', 'VOU-1771955656703', '2026-02-24', 'Fuel', '231', NULL, NULL, 'Cash', NULL, NULL, NULL, 'Dummy Payment', '1', '1', '2026-02-24 17:54:16');
INSERT INTO `driver_payments` VALUES ('5', '2', 'VOU-1771955656459', '2026-02-24', 'Urea', '940', NULL, NULL, 'Cash', NULL, NULL, NULL, 'Dummy Payment', '1', '1', '2026-02-24 17:54:16');

DROP TABLE IF EXISTS `documents`;
CREATE TABLE `documents` (
    id INTEGER,
    ref_table VARCHAR(50) NOT NULL,
    ref_id INTEGER NOT NULL,
    document_name VARCHAR(100) NOT NULL,
    document_number VARCHAR(100),
    file_path VARCHAR(255),
    expiry_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO `documents` VALUES ('1', 'vehicles', '1', 'rc', '002255', '../uploads/documents/vehicles/vehicle_1_699ddd3096b09.pdf', NULL, '2026-02-24 17:17:36');
INSERT INTO `documents` VALUES ('2', 'drivers', '1', 'rc', '002255', '../uploads/documents/drivers/driver_1_699de16242488.pdf', NULL, '2026-02-24 17:35:30');
INSERT INTO `documents` VALUES ('3', 'drivers', '1', 'rc', '22222', '../uploads/documents/drivers/driver_1_699de1624460d.docx', NULL, '2026-02-24 17:35:30');

DROP TABLE IF EXISTS `vehicle_driver_assign`;
CREATE TABLE `vehicle_driver_assign` (
    id INTEGER,
    vehicle_id INTEGER,
    driver_id INTEGER,
    assigned_date DATE,
    doc_rc_path TEXT,
    doc_license_path TEXT,
    doc_insurance_path TEXT,
    doc_other_path TEXT,
    remarks TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT,
    opening_km INTEGER,
    closing_km INTEGER,
    PRIMARY KEY (id)
);

INSERT INTO `vehicle_driver_assign` VALUES ('1', '1', '1', '2026-02-24', NULL, NULL, NULL, NULL, '', '2026-02-24 17:40:28', NULL, NULL, NULL);
INSERT INTO `vehicle_driver_assign` VALUES ('2', '5', '5', '2026-02-19', NULL, NULL, NULL, NULL, 'Dummy Assignment', '2026-02-24 17:54:16', NULL, '29047', '29516');
INSERT INTO `vehicle_driver_assign` VALUES ('3', '4', '5', '2026-02-20', NULL, NULL, NULL, NULL, 'Dummy Assignment', '2026-02-24 17:54:16', NULL, '13998', '14382');
INSERT INTO `vehicle_driver_assign` VALUES ('4', '5', '2', '2026-02-02', NULL, NULL, NULL, NULL, 'Dummy Assignment', '2026-02-24 17:54:16', NULL, '19308', '19762');
INSERT INTO `vehicle_driver_assign` VALUES ('5', '1', '4', '2026-02-12', NULL, NULL, NULL, NULL, 'Dummy Assignment', '2026-02-24 17:54:16', NULL, '14534', '14804');

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
    id INTEGER,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    description TEXT,
    PRIMARY KEY (id)
);

INSERT INTO `settings` VALUES ('1', 'enable_ewaybill', '0', 'Enable E-Way Bill Integration');
INSERT INTO `settings` VALUES ('2', 'enable_einvoice', '0', 'Enable E-Invoice Integration');
