-- SRPV LOGISTICS - MySQL Database Schema for Hostinger
-- Can be imported directly via Hostinger phpMyAdmin

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+05:30";

-- Table structure for `clients`
CREATE TABLE IF NOT EXISTS `clients` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_name` VARCHAR(255) NOT NULL UNIQUE,
  `address` TEXT DEFAULT NULL,
  `gst_no` VARCHAR(50) DEFAULT '',
  `state` VARCHAR(100) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table structure for `daily_entries`
CREATE TABLE IF NOT EXISTS `daily_entries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `entry_type` VARCHAR(50) NOT NULL DEFAULT 'courier',
  `entry_date` DATE NOT NULL,
  `docket_no` VARCHAR(100) DEFAULT '',
  `vehicle_no` VARCHAR(100) DEFAULT '',
  `vehicle_size` VARCHAR(100) DEFAULT '',
  `from_location` VARCHAR(150) DEFAULT '',
  `to_location` VARCHAR(150) DEFAULT '',
  `uom` VARCHAR(50) DEFAULT 'KG',
  `no_of_cases` INT DEFAULT 0,
  `actual_weight` DECIMAL(10,2) DEFAULT 0.00,
  `charged_weight` DECIMAL(10,2) DEFAULT 0.00,
  `rate` DECIMAL(10,2) DEFAULT 0.00,
  `amount` DECIMAL(10,2) DEFAULT 0.00,
  `client_name` VARCHAR(255) DEFAULT '',
  `remarks` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`entry_date`),
  INDEX (`entry_type`),
  INDEX (`client_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table structure for `invoices`
CREATE TABLE IF NOT EXISTS `invoices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `invoice_no` VARCHAR(100) NOT NULL,
  `invoice_type` VARCHAR(50) NOT NULL DEFAULT 'courier',
  `invoice_date` DATE NOT NULL,
  `from_date` DATE DEFAULT NULL,
  `to_date` DATE DEFAULT NULL,
  `client_name` VARCHAR(255) DEFAULT '',
  `client_address` TEXT DEFAULT NULL,
  `client_gst` VARCHAR(50) DEFAULT '',
  `client_state` VARCHAR(100) DEFAULT '',
  `subtotal` DECIMAL(12,2) DEFAULT 0.00,
  `gst_rate` DECIMAL(5,2) DEFAULT 18.00,
  `gst_amount` DECIMAL(12,2) DEFAULT 0.00,
  `grand_total` DECIMAL(12,2) DEFAULT 0.00,
  `remarks` TEXT DEFAULT NULL,
  `items_json` LONGTEXT DEFAULT NULL,
  `extra_data` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`invoice_no`),
  INDEX (`invoice_date`),
  INDEX (`invoice_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default Client
INSERT INTO `clients` (`client_name`, `address`, `gst_no`, `state`) VALUES
('Apollo Healthco Limited', 'Ground Floor, Plot No-12/2 Milestone, Delhi Mathura Road, Faridabad\nHARYANA-121003', '06AATCA5622L1ZA', 'Haryana')
ON DUPLICATE KEY UPDATE `address`=VALUES(`address`);

-- Sample Daily Entries matching Tax Invoice sample (LKO/024/2026-2027)
INSERT INTO `daily_entries` (`entry_type`, `entry_date`, `docket_no`, `from_location`, `to_location`, `uom`, `no_of_cases`, `actual_weight`, `charged_weight`, `rate`, `amount`, `client_name`) VALUES
('courier', '2026-08-03', '420', 'FBD', 'VNS', 'KG', 4, 15.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-03', '422', 'FBD', 'MOHDI', 'BOX', 7, 60.00, 7.00, 233.00, 1632.00, 'Apollo Healthco Limited'),
('courier', '2026-08-03', '421', 'FBD', 'AYD', 'KG', 14, 127.00, 127.00, 10.00, 1270.00, 'Apollo Healthco Limited'),
('courier', '2026-08-03', '419', 'FBD', 'FBD', 'KG', 8, 93.00, 93.00, 10.00, 930.00, 'Apollo Healthco Limited'),
('courier', '2026-08-03', '417', 'AYD', 'KG', 'KG', 10, 85.00, 85.00, 10.00, 850.00, 'Apollo Healthco Limited'),
('courier', '2026-08-03', '418', 'FBD', 'GKP', 'KG', 15, 132.00, 132.00, 10.00, 1320.00, 'Apollo Healthco Limited'),
('courier', '2026-08-05', '427', 'FBD', 'VNS', 'KG', 3, 10.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-05', '428', 'FBD', 'MOHDI', 'BOX', 2, 2.00, 2.00, 351.00, 702.00, 'Apollo Healthco Limited'),
('courier', '2026-08-05', '426', 'FBD', 'AYD', 'KG', 10, 59.00, 59.00, 10.00, 590.00, 'Apollo Healthco Limited'),
('courier', '2026-08-05', '425', 'FBD', 'VNS', 'KG', 6, 32.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-05', '423', 'FBD', 'AYD', 'KG', 5, 36.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-05', '424', 'FBD', 'GKP', 'KG', 12, 89.00, 89.00, 10.00, 890.00, 'Apollo Healthco Limited'),
('courier', '2026-08-07', '430', 'FBD', 'VNS', 'KG', 6, 19.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-07', '434', 'FBD', 'MOHDI', 'BOX', 3, 2.00, 3.00, 296.00, 888.00, 'Apollo Healthco Limited'),
('courier', '2026-08-07', '431', 'FBD', 'AYD', 'KG', 26, 250.00, 250.00, 10.00, 2500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-07', '433', 'FBD', 'VNS', 'KG', 4, 17.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-07', '432', 'FBD', 'AYD', 'KG', 32, 253.00, 253.00, 10.00, 2530.00, 'Apollo Healthco Limited'),
('courier', '2026-08-07', '429', 'FBD', 'GKP', 'KG', 30, 271.00, 271.00, 10.00, 2710.00, 'Apollo Healthco Limited'),
('courier', '2026-08-10', '435', 'FBD', 'VNS', 'KG', 7, 47.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited'),
('courier', '2026-08-10', '440', 'FBD', 'MOHDI', 'BOX', 7, 23.00, 7.00, 233.00, 1632.00, 'Apollo Healthco Limited'),
('courier', '2026-08-10', '438', 'FBD', 'AYD', 'KG', 17, 110.00, 110.00, 10.00, 1100.00, 'Apollo Healthco Limited'),
('courier', '2026-08-10', '437', 'FBD', 'VNS', 'KG', 7, 41.00, 50.00, 10.00, 500.00, 'Apollo Healthco Limited');

COMMIT;