-- Branches Table
CREATE TABLE IF NOT EXISTS `branches` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `location` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20),
    `email` VARCHAR(100),
    `manager_id` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Car Images Table (Multiple Images)
CREATE TABLE IF NOT EXISTS `car_images` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `car_id` INT NOT NULL,
    `image_url` VARCHAR(255) NOT NULL,
    `is_primary` TINYINT(1) DEFAULT 0,
    FOREIGN KEY (`car_id`) REFERENCES `cars`(`id`) ON DELETE CASCADE
);

-- Sales Table
CREATE TABLE IF NOT EXISTS `sales` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `car_id` INT NOT NULL,
    `buyer_id` INT NOT NULL,
    `seller_id` INT, -- Dealer or Admin who sold it
    `sale_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `final_price` DECIMAL(15, 2) NOT NULL,
    `payment_method` ENUM('Cash', 'Installment') DEFAULT 'Cash',
    `status` ENUM('Pending', 'Completed', 'Cancelled') DEFAULT 'Pending',
    FOREIGN KEY (`car_id`) REFERENCES `cars`(`id`),
    FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`)
);

-- Installments Table
CREATE TABLE IF NOT EXISTS `installments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `sale_id` INT NOT NULL,
    `total_amount` DECIMAL(15, 2) NOT NULL,
    `paid_amount` DECIMAL(15, 2) DEFAULT 0.00,
    `monthly_payment` DECIMAL(15, 2) NOT NULL,
    `due_date` DATE,
    `status` ENUM('Active', 'Completed', 'Defaulted') DEFAULT 'Active',
    FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE
);

-- Activity Logs
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT,
    `action` VARCHAR(255) NOT NULL,
    `details` TEXT,
    `ip_address` VARCHAR(45),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Notifications
CREATE TABLE IF NOT EXISTS `notifications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `message` TEXT NOT NULL,
    `is_read` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- Update Cars Table
ALTER TABLE `cars` ADD COLUMN IF NOT EXISTS `dealer_id` INT DEFAULT NULL;
ALTER TABLE `cars` ADD COLUMN IF NOT EXISTS `installment_available` TINYINT(1) DEFAULT 0;

-- Update Users Table (Ensure role column is correct - strictly speaking it already exists but this is safe)
-- ALTER TABLE `users` MODIFY COLUMN `role` ENUM('Guest','Customer','Dealer','Employee','Manager','Admin','Super Admin') DEFAULT 'Customer';