-- 18-04-2025

ALTER TABLE `users` ADD `role` VARCHAR(25) NULL DEFAULT NULL AFTER `deleted_at`;

CREATE TABLE `counter` (`counter_id` INT NOT NULL AUTO_INCREMENT , `counter_name` VARCHAR(50) NOT NULL , `counter_value` INT NOT NULL , PRIMARY KEY (`counter_id`)) ENGINE = MyISAM;

ALTER TABLE `counter` ADD `counter_prefix` VARCHAR(25) NULL DEFAULT NULL AFTER `counter_value`;

CREATE TABLE organizations ( organization_id INT AUTO_INCREMENT PRIMARY KEY, organization_name VARCHAR(255) NOT NULL, organization_address TEXT NOT NULL, organization_website VARCHAR(255), organization_phone VARCHAR(20) NOT NULL, organization_alternate_phone VARCHAR(20), organization_email VARCHAR(255) NOT NULL, organization_logo VARCHAR(255), organization_gstin_number VARCHAR(20), organization_pan_number VARCHAR(20), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

CREATE TABLE `categories` (
  `category_id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_name` VARCHAR(255) NOT NULL UNIQUE,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `tax` (
  `tax_id` INT AUTO_INCREMENT PRIMARY KEY,
  `tax_name` VARCHAR(255) NOT NULL,
  `tax_percent` DECIMAL(5,2) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE units (
    unit_id INT AUTO_INCREMENT PRIMARY KEY,
    unit_name VARCHAR(100) NOT NULL UNIQUE,
    unit_description VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_category INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_hsn_code VARCHAR(50),
    product_description TEXT,
    product_rate DECIMAL(10, 2) NOT NULL,
    product_unit INT NOT NULL,
    product_quantity INT DEFAULT 0,
    product_tax INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (product_category) REFERENCES categories(category_id),
    FOREIGN KEY (product_unit) REFERENCES units(unit_id),
    FOREIGN KEY (product_tax) REFERENCES tax(tax_id)
);


CREATE TABLE `email_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email_protocol` VARCHAR(50) NOT NULL,
    `email_hostname` VARCHAR(100) NOT NULL,
    `email_portno` INT NOT NULL,
    `email_id` VARCHAR(100) NOT NULL,
    `email_password` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE term_conditions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    terms TEXT NOT NULL,
    remarks TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


ALTER TABLE `leads` ADD `email` VARCHAR(255) NULL AFTER `is_converted`;

ALTER TABLE `products` ADD `product_make` VARCHAR(255) NULL AFTER `updated_at`;

ALTER TABLE `leads` ADD `resource_details` VARCHAR(100) NULL AFTER `email`;

CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    supplier_name VARCHAR(255) NOT NULL,
    address TEXT,
    mobile_number VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

CREATE TABLE stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    make VARCHAR(100),
    quantity DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE material_requisitions (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL, -- FK to customers table
material_requisition_form_no VARCHAR(20) UNIQUE NOT NULL,
requisition_date DATE,
project_size VARCHAR(100),
address TEXT,
initiated_by VARCHAR(100),
authorization VARCHAR(100),
project_type VARCHAR(50),
reason TEXT,
expected_usage_date DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customer_application(customer_id)
);

CREATE TABLE material_requisition_items (
id INT AUTO_INCREMENT PRIMARY KEY,
requisition_id INT NOT NULL, -- FK to material_requisitions
product_id INT, -- FK to products (id)
description TEXT,
vendor_id INT, -- FK to suppliers(id)
quantity INT,
FOREIGN KEY (requisition_id) REFERENCES material_requisitions(id)
);	

INSERT INTO `counter` (`counter_name`, `counter_value`)
VALUES ('Application', '01');

ALTER TABLE `users` ADD `profile_photo` VARCHAR(255) NULL DEFAULT NULL AFTER `is_deleted`;

ALTER TABLE `material_requisition_items` ADD `received_quantity` INT NULL AFTER `quantity`;
ALTER TABLE `material_requisitions` ADD `is_received` VARCHAR(50) NULL DEFAULT NULL AFTER `created_at`;



CREATE TABLE customer_form (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
applicant_name VARCHAR(255),
installation_address TEXT,
consumer_email VARCHAR(255),
customer_mobile_number VARCHAR(25),
consumer_number VARCHAR(20),
billing_unit VARCHAR(4), -- NEW: Billing unit (exactly 4-digit string)
consumer_type VARCHAR(20), -- NEW: Type of Consumer (e.g., Urban, Rural)
proposed_solar_system VARCHAR(100),
date_of_visit DATE,
mseb_sanction_load_kw DECIMAL(4,2),
solar_capacity_install_kw DECIMAL(4,2),
apply_load_sanction BOOLEAN,
load_sanction_link TEXT,
need_to_increase_load BOOLEAN,
load_increased BOOLEAN,
minimum_load_type VARCHAR(100),
free_area_ground_sqm DECIMAL(10,2),
roof_type VARCHAR(20),
tilt_type VARCHAR(20),
inverter_acdc_space TEXT,
meter_room_available BOOLEAN,
generator_meter_cabin_required BOOLEAN,
duct_available BOOLEAN,
duct_details TEXT,
earthing_details TEXT,
application_for_solar BOOLEAN,
solar_application_link TEXT,
customer_finalised BOOLEAN,
date_of_finalisation DATE
);

ALTER TABLE customer_documents DROP FOREIGN KEY customer_documents_ibfk_1, ADD CONSTRAINT fk_customer_documents_customer_id FOREIGN KEY (`customer_id`) REFERENCES `customer_application` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `customer_documents` ADD `application_id` INT NULL DEFAULT NULL AFTER `installed_meter_image_file`;

ALTER TABLE `users` CHANGE `role` `role` VARCHAR(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL;

ALTER TABLE `material_requisitions` ADD `customer_application_number` INT NULL DEFAULT NULL AFTER `is_received`;

ALTER TABLE `material_requisitions` CHANGE `customer_application_number` `customer_application_number` VARCHAR(500) NULL DEFAULT NULL;

ALTER TABLE `material_requisitions` ADD `created_by` INT NULL DEFAULT NULL AFTER `customer_application_number`;

ALTER TABLE `material_requisitions` ADD `updated_by` INT NULL DEFAULT NULL AFTER `created_by`;

CREATE TABLE stock_inward (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL,
    vendor_id INT,
    make VARCHAR(100),
    received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT NOT NULL,
    updated_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);


ALTER TABLE `stock_inward` ADD `material_requisition_item_id` INT NULL DEFAULT NULL AFTER `updated_at`;


ALTER TABLE `leads` ADD `created_by` INT(11) NULL AFTER `resource_details`;

ALTER TABLE `leads` ADD `updated_by` INT(11) NULL AFTER `created_by`;


ALTER TABLE `customer_form` ADD `created_at` DATE NULL AFTER `consumer_type`;

ALTER TABLE `customer_form` ADD `created_by` INT(11) NOT NULL AFTER `created_at`;

ALTER TABLE `customer_form` ADD `updated_by` INT(11) NOT NULL AFTER `created_by`;

ALTER TABLE `customer_form` ADD `updated_at` DATE  NULL AFTER `updated_by`;



ALTER TABLE `customer_application` ADD `created_at` DATE NULL AFTER `balance_pending`;


ALTER TABLE `customer_application` ADD `created_by` INT(11) NOT NULL AFTER `created_at`;


ALTER TABLE `customer_application` ADD `updated_by` INT(11) NOT NULL AFTER `created_by`;


ALTER TABLE `customer_application` ADD `updated_at` DATE  NULL AFTER `updated_by`;


CREATE TABLE reminders (
    id INT(11) NOT NULL AUTO_INCREMENT,
    application_id INT(11) NOT NULL,
    customer_id INT(11) NOT NULL,
    from_field VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    to_field VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    deadline_date DATE NOT NULL,
    message TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    status ENUM('pending', 'overdue', 'resolved') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'pending',
    deadline_days INT(3) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    created_by INT(11) NOT NULL,
    updated_by INT(11) DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX (application_id),
    INDEX (customer_id),
    INDEX (status),
    INDEX (created_by),
    INDEX (updated_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE stock_outward (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL,
    application_no VARCHAR(100),
    delivery_challan_id INT,  -- Added the data type
    make VARCHAR(100),
    invoice_no VARCHAR(100),
    issued_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT NOT NULL,
    updated_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE bom (			
	id INT AUTO_INCREMENT PRIMARY KEY,			
	customer_id INT NOT NULL,			
	application_number VARCHAR(50),			
	contact_info VARCHAR(20),			
				
	-- Group 1			
	solar_pv_module_spec TEXT,			
	solar_pv_module_make VARCHAR(100),			
	solar_pv_module_qty VARCHAR(50),			
				
	solar_inverter_spec TEXT,			
	solar_inverter_make VARCHAR(100),			
	solar_inverter_qty VARCHAR(50),			
				
	solar_panel_mounting_structure_spec TEXT,			
	solar_panel_mounting_structure_make VARCHAR(100),			
	solar_panel_mounting_structure_qty VARCHAR(50),			
				
	-- Group 2			
	acdb_spec TEXT,			
	acdb_make VARCHAR(100),			
	acdb_qty VARCHAR(50),			
				
	dcdb_spec TEXT,			
	dcdb_make VARCHAR(100),			
	dcdb_qty VARCHAR(50),			
				
	solar_net_meter_spec TEXT,			
	solar_net_meter_make VARCHAR(100),			
	solar_net_meter_qty VARCHAR(50),			
				
	-- Group 3 (No Make)			
	dc_cable_spec TEXT,			
	dc_cable_qty VARCHAR(50),			
				
	ac_cable_spec TEXT,			
	ac_cable_qty VARCHAR(50),			
				
	installation_spec TEXT,			
	installation_qty VARCHAR(50),			
				
	transport_spec TEXT,			
	transport_qty VARCHAR(50),			
				
	meter_work_spec TEXT,			
	meter_work_qty VARCHAR(50),			
				
	lesioning_spec TEXT,			
	lesioning_qty VARCHAR(50),			
				
	-- Audit Fields			

	created_by VARCHAR(100),			
	created_date DATETIME DEFAULT CURRENT_TIMESTAMP,			
	updated_by VARCHAR(100),			
	updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,			
				
	FOREIGN KEY (customer_id) REFERENCES customer_form(customer_id)			
	);




ALTER TABLE `bom` ADD `solar_generation_meter_spec` TEXT NULL DEFAULT NULL AFTER `lesioning_qty`, ADD `solar_generation_meter_make` VARCHAR(255) NULL DEFAULT NULL AFTER `solar_generation_meter_spec`, ADD `solar_generation_meter_qty` VARCHAR(50) NULL DEFAULT NULL AFTER `solar_generation_meter_make`;
ALTER TABLE `bom` ADD `dc_cable_make` VARCHAR(100) NULL DEFAULT NULL AFTER `dc_cable_qty`;
ALTER TABLE `bom` ADD `ac_cable_make` VARCHAR(100) NULL DEFAULT NULL AFTER `ac_cable_qty`;
ALTER TABLE `bom` ADD `earthing_plats_strips_or_cable_spec` TEXT NULL DEFAULT NULL AFTER `solar_generation_meter_qty`, ADD `earthing_plats_strips_or_cable_make` VARCHAR(100) NULL DEFAULT NULL AFTER `earthing_plats_strips_or_cable_spec`, ADD `earthing_plats_strips_or_cable_qty` VARCHAR(50) NULL DEFAULT NULL AFTER `earthing_plats_strips_or_cable_make`;

ALTER TABLE `products` ADD `product_type` VARCHAR(50) NULL DEFAULT NULL AFTER `product_make`;

ALTER TABLE `stock_inward` ADD `application_number` VARCHAR(255) NULL DEFAULT NULL AFTER `material_requisition_item_id`, ADD `type` VARCHAR(50) NULL DEFAULT NULL AFTER `application_number`;

ALTER TABLE `customer_application` ADD `is_bom_make` BOOLEAN NULL DEFAULT NULL AFTER `updated_at`;


INSERT INTO `counter` (`counter_id`, `counter_name`, `counter_value`, `counter_prefix`) VALUES (NULL, 'delivery_challan_no', '001', 'SEPLST')

ALTER TABLE `stock` CHANGE `quantity` `quantity` INT NOT NULL;

INSERT INTO `counter` (`counter_name`, `counter_value`)
VALUES ('requisition ', '01');

CREATE TABLE IF NOT EXISTS stock_confirmation (
    stock_confirmation_id INT AUTO_INCREMENT PRIMARY KEY,
    stock_id INT NOT NULL,
    quantity INT DEFAULT 0,
    actual_quantity INT DEFAULT 0,
    confirmed_by VARCHAR(100) DEFAULT NULL,
    confirmation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (stock_id) REFERENCES stock(id) ON DELETE CASCADE,
    INDEX (stock_id)
);

ALTER TABLE `stock` ADD `created_at` TIMESTAMP NULL DEFAULT NULL AFTER `quantity`, ADD `updated_at` TIMESTAMP NULL DEFAULT NULL AFTER `created_at`;

CREATE TABLE delivery_challan (
    id INT(11) NOT NULL AUTO_INCREMENT,
    number_type ENUM('application', 'invoice') NOT NULL,
    application_no VARCHAR(100) DEFAULT NULL,
    invoice_no VARCHAR(100) DEFAULT NULL,
    delivery_challan_no VARCHAR(100) NOT NULL,
    delivery_status VARCHAR(50) NOT NULL,
    confirmation_person VARCHAR(100) NOT NULL,
    is_payment_received TINYINT(1) NOT NULL DEFAULT 1,
    admin_confirmation TEXT DEFAULT NULL,
    admin_approved TINYINT(1) DEFAULT NULL,
    admin_approved_by INT(11) DEFAULT NULL,
    created_by INT(11) NOT NULL,
    updated_by INT(11) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT NULL,
    rejected_challan_reason VARCHAR(200) DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX (delivery_challan_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `delivery_challan_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `challan_id` int(11) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `challan_id_idx` (`challan_id`),
  KEY `product_id_idx` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `suppliers` ADD `sales_person` VARCHAR(50) NULL DEFAULT NULL AFTER `created_at`;

CREATE TABLE `notifications` (
  `notification_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `message` TEXT NOT NULL,
  `status` ENUM('Unread', 'Read') NOT NULL DEFAULT 'Unread',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `related_id` INT NULL,
  `related_type` VARCHAR(100) NULL,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_related` (`related_id`, `related_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `delivery_challan` ADD `is_deleted` TINYINT(2) NULL AFTER `rejected_challan_reason`;
ALTER TABLE `stock_inward` CHANGE `quantity` `quantity` INT NOT NULL;
ALTER TABLE `stock_outward` CHANGE `quantity` `quantity` INT NOT NULL;



ALTER TABLE `customer_form` ADD `alternative_mobile_number` VARCHAR(25) NULL DEFAULT NULL AFTER `updated_at`;


-- 26-05-2025 Madhuri
ALTER TABLE `bom` ADD `rccb_spec` TEXT NULL DEFAULT NULL AFTER `ac_cable_make`, ADD `rccb_make` VARCHAR(100) NULL DEFAULT NULL AFTER `rccb_spec`, ADD `rccb_qty` INT NULL DEFAULT NULL AFTER `rccb_make`;

ALTER TABLE `customer_form` ADD `alternative_mobile_number` VARCHAR(25) NULL DEFAULT NULL AFTER `updated_at`;
ALTER TABLE delivery_challan ADD COLUMN invoice_customer_name VARCHAR(255) DEFAULT NULL;
ALTER TABLE delivery_challan ADD COLUMN invoice_customer_address TEXT DEFAULT NULL;
ALTER TABLE delivery_challan ADD COLUMN invoice_customer_mobile VARCHAR(20) DEFAULT NULL;

-- 16-05-2025 Shubham
ALTER TABLE `customer_documents` ADD `inverter_certificate` VARCHAR(255) NULL DEFAULT NULL AFTER `application_id`;


-- 27-05-2025 Shubham
ALTER TABLE notifications 
ADD COLUMN resolved_by INT(11) DEFAULT NULL AFTER status,
ADD COLUMN resolved_at DATETIME DEFAULT NULL AFTER resolved_by;


-- 29-05-2025
ALTER TABLE `leads` CHANGE `is_converted` `is_converted` INT(11) NULL DEFAULT '0';

-- 02-06-2025 Madhuri
ALTER TABLE `customer_form` CHANGE `proposed_solar_system` `proposed_solar_system` TEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL;

-- 16-06-2025
ALTER TABLE `delivery_challan` ADD `invoice_gst_number` VARCHAR(100) NULL DEFAULT NULL AFTER `invoice_customer_mobile`;

-- 20-06-2025
ALTER TABLE `customer_application` ADD `reason_subsidy_received_date` TEXT NULL DEFAULT NULL AFTER `is_bom_make`, ADD `authorised_person_subsidy_received_date` TEXT NULL DEFAULT NULL AFTER `reason_subsidy_received_date`;