-- Fomax Logistics Database Schema

-- Create database

-- Branches Table
CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Riders Table
CREATE TABLE riders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20),
    branch_id INT,
    profile_picture VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- Records Table
CREATE TABLE records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rider_id INT,
    package_description VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    item_valuation DECIMAL(10, 2) NOT NULL,
    location TEXT,
    vendor_charges DECIMAL(10, 2) DEFAULT 0.00,
    payment_mode VARCHAR(50),
    notes TEXT,
    user_id INT,
    branch_id INT,
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (rider_id) REFERENCES riders(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- Expenses Table
CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description TEXT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    record_id INT NULL,
    user_id INT,
    branch_id INT,
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (record_id) REFERENCES records(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- Insert sample data for testing
INSERT INTO branches (name, location) VALUES 
('Main Office', '123 Logistics Street, Main City'),
('Island Branch', '456 Island Road, Island City');

INSERT INTO riders (name, phone_number, branch_id) VALUES 
('John Delivery', '123-456-7890', 1),
('Jane Express', '098-765-4321', 2);

-- Sample records
INSERT INTO records (rider_id, package_description, quantity, item_valuation, location, vendor_charges, payment_mode, notes, user_id, branch_id) VALUES 
(1, 'Electronics Package', 2, 150.00, '789 Delivery Ave', 20.00, 'Cash', 'Fragile items', 2, 1),
(2, 'Documents Bundle', 1, 25.00, '321 Business Blvd', 10.00, 'Transfer', 'Urgent delivery', 3, 2);

-- Sample expenses
INSERT INTO expenses (description, amount, record_id, user_id, branch_id) VALUES 
('Fuel for delivery van', 50.00, 1, 2, 1),
('Packaging materials', 15.50, NULL, 3, 2);