db.text

CREATE TABLE `optical_system`.`customers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mobile_number` VARCHAR(45) NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `nic_number` VARCHAR(45) NULL,
  `address` TEXT NULL,
  `gender` VARCHAR(45) NULL CHECK (`gender` IN ('Male', 'Female', 'Other')),
  PRIMARY KEY (`id`)
);

  
 CREATE TABLE `optical_system`.`prescriptions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT NOT NULL,
  `right_sph` VARCHAR(255),
  `right_cyl` VARCHAR(255),
  `right_axis` VARCHAR(255),
  `left_sph` VARCHAR(255),
  `left_cyl` VARCHAR(255),
  `left_axis` VARCHAR(255),
  `left_add` VARCHAR(255),
  `right_add` VARCHAR(255),
  `date_prescribed` DATE,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
  INDEX `idx_customer` (`customer_id` ASC)
);



CREATE TABLE `optical_system`.`billings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT NOT NULL,
  `invoice_date` DATE NOT NULL,
  `delivery_date` DATE,  -- This line adds the delivery_date column which can be NULL
  `sales_person` VARCHAR(100),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`)
);

ALTER TABLE `optical_system`.`billings` ADD COLUMN `delivery_date` DATE;

CREATE TABLE `optical_system`.`billing_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `billing_id` INT NOT NULL,
  `frame_id` INT NOT NULL,
  `lens_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `unit_price` FLOAT NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`billing_id`) REFERENCES `billings`(`id`)
);
ALTER TABLE `optical_system`.`billing_items`
DROP COLUMN `quantity`,
DROP COLUMN `unit_price`;

ALTER TABLE `optical_system`.`billing_items`
ADD COLUMN `frame_qty` INT NOT NULL,
ADD COLUMN `lens_qty` INT NOT NULL;
//

CREATE TABLE `optical_system`.`payment_details` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `billing_id` INT NOT NULL,
  `total_amount` FLOAT NOT NULL,
  `discount` FLOAT,
  `fitting_charges` FLOAT,
  `grand_total` FLOAT NOT NULL,
  `advance_paid` FLOAT,
  `balance_amount` FLOAT,
  `pay_type` VARCHAR(50),  -- E.g., 'cash', 'credit card', 'check'
  PRIMARY KEY (`id`),
  FOREIGN KEY (`billing_id`) REFERENCES `billings`(`id`)
);

CREATE TABLE `optical_system`.`lenses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `lens_type` VARCHAR(50) NOT NULL,
  `material` VARCHAR(50),
  `coating` VARCHAR(50),
  `prescription_type` VARCHAR(50),
  `price` FLOAT NOT NULL,
  `quantity` INT NOT NULL,
  `selling_price` FLOAT NOT NULL,
  PRIMARY KEY (`id`));
  
  CREATE TABLE `optical_system`.`frames` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `brand` VARCHAR(50) NOT NULL,
  `frames` VARCHAR(50),
  `material` VARCHAR(50),
  `color` VARCHAR(50),
  `size` VARCHAR(50),
  `price` FLOAT NOT NULL, -- This may be considered the cost price.
  `selling_price` FLOAT NOT NULL, -- This is the price at which the frame is sold to customers.
  PRIMARY KEY (`id`));



CREATE TABLE optical_system.optical_shops (
    id INT PRIMARY KEY AUTO_INCREMENT,
    shop_name VARCHAR(255) NOT NULL UNIQUE,
    head_office_address TEXT,
    contact_number VARCHAR(45),
    email VARCHAR(255) UNIQUE
);


CREATE TABLE optical_system.branches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    branch_name VARCHAR(255),
    branch_code VARCHAR(255) UNIQUE,
    shop_id INT,
    mobile_number VARCHAR(45),
    FOREIGN KEY (shop_id) REFERENCES optical_shops(id)
);

CREATE TABLE optical_system.users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255),
    branch_id INT,
    role VARCHAR(50),
    FOREIGN KEY (branch_id) REFERENCES optical_system.branches(id)
);

ALTER TABLE `optical_system`.`frames`
ADD COLUMN `branch_id` INT,
ADD CONSTRAINT `fk_frames_branch`
FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`);

ALTER TABLE `optical_system`.`lenses`
ADD COLUMN `branch_id` INT,
ADD CONSTRAINT `fk_lenses_branch`
FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`);

ALTER TABLE `optical_system`.`customers`
ADD COLUMN `branch_id` INT,
ADD CONSTRAINT `fk_customers_branch`
FOREIGN KEY (`branch_id`) REFERENCES `optical_system`.`branches`(`id`);