-- Add Locations/Branches Table
CREATE TABLE IF NOT EXISTS locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(100),
    state VARCHAR(100),
    zip_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'United States',
    phone VARCHAR(20),
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Add location_id to orders table
ALTER TABLE orders ADD COLUMN location_id INT NULL AFTER order_type;
ALTER TABLE orders ADD FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Insert default locations
INSERT INTO locations (name, address, city, state, zip_code, country, phone, email) VALUES
('Wilmington Main Branch', '123 Main Street', 'Wilmington', 'Delaware', '19801', 'United States', '+1 (914) 520 5495', 'info@orderfood.com'),
('Newark Branch', '456 Commerce Drive', 'Newark', 'Delaware', '19711', 'United States', '+1 (302) 555-1234', 'newark@orderfood.com'),
('Dover Branch', '789 Market Street', 'Dover', 'Delaware', '19901', 'United States', '+1 (302) 555-5678', 'dover@orderfood.com');


