CREATE TABLE
    organization (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    );

CREATE TABLE
    office (
        id INT AUTO_INCREMENT PRIMARY KEY,
        organization_id INT,
        name VARCHAR(255),
        sender_email VARCHAR(255),
        host VARCHAR(255),
        password VARCHAR(255),
        port INT,
        FOREIGN KEY (organization_id) REFERENCES organization (id)
    );

CREATE TABLE
    category_level_one (
        id INT AUTO_INCREMENT PRIMARY KEY,
        office_id INT,
        organization_id int,
        name VARCHAR(255),
        FOREIGN KEY (organization_id) REFERENCES organization (id),
        FOREIGN KEY (office_id) REFERENCES office (id)
    );

CREATE TABLE
    category_level_two (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category_level_one_id int,
        name VARCHAR(255),
        FOREIGN KEY (category_level_one_id) REFERENCES category_level_one (id)
    );

CREATE TABLE
    category_level_three (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category_level_two_id int,
        name VARCHAR(255),
        FOREIGN KEY (category_level_two_id) REFERENCES category_level_two (id)
    );

CREATE TABLE
    category_level_four (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category_level_three_id int,
        name VARCHAR(255),
        FOREIGN KEY (category_level_three_id) REFERENCES category_level_three (id)
    );

CREATE TABLE
    users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        full_name VARCHAR(50) NOT NULL,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL,
        password VARCHAR(255) NOT NULL,
        role_id INT NOT NULL,
        office_id INT NOT NULL,
        cnic VARCHAR(15) NOT NULL,
        mobile_no VARCHAR(15) NOT NULL,
        landline VARCHAR(15)
    );

CREATE TABLE
    enquiries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        enquiry_by TEXT,
        enquiry_entered_by TEXT,
        income_range TEXT,
        enquiry_date TEXT,
        username TEXT,
        cnic TEXT,
        date_of_birth TEXT,
        email TEXT,
        father_name TEXT,
        gender TEXT,
        city TEXT,
        present_address TEXT,
        permanent_address TEXT,
        mobile_no_one TEXT,
        mobile_no_two TEXT,
        landline TEXT,
        project TEXT,
        product TEXT,
        developer TEXT,
        price_offered TEXT,
        property_address TEXT,
        image TEXT,
        source_of_information TEXT,
        status TEXT,
        enquiry_status TEXT,
        remarks TEXT,
        field_one TEXT,
        field_two TEXT,
        field_three TEXT,
        field_four TEXT,
        field_five TEXT,
        field_six TEXT,
        field_seven TEXT,
        field_eight TEXT,
        field_nine TEXT,
        field_ten TEXT,
        office_id int,
        matured TEXT,
        FOREIGN KEY (office_id) REFERENCES office (id),
        cat_one_id int,
        FOREIGN KEY (cat_one_id) REFERENCES category_level_one (id),
        cat_two_id int,
        FOREIGN KEY (cat_two_id) REFERENCES category_level_two (id),
        cat_three_id int,
        FOREIGN KEY (cat_three_id) REFERENCES category_level_three (id),
        cat_four_id int,
        FOREIGN KEY (cat_four_id) REFERENCES category_level_four (id)
    );

CREATE TABLE
    roles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        role VARCHAR(255) NOT NULL,
        self_inquiries BOOLEAN
    );

CREATE TABLE
    enquiry_fields (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        field_name VARCHAR(255) NOT NULL,
        status TINYINT (1),
        report_show TINYINT (1),
        order_no INT
    );

CREATE TABLE
    assign_enquiries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        enquiry_id INT NOT NULL,
        status TINYINT (1) NOT NULL,
        date DATETIME NOT NULL
    );

CREATE TABLE
    follow_up (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        enquiry_id INT,
        called_by VARCHAR(255),
        called_type VARCHAR(255),
        called_status VARCHAR(255),
        remarks TEXT,
        price_offered VARCHAR(255),
        followup_date DATE,
        next_followup_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE
    permissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        role_id BOOLEAN,
        FOREIGN KEY (role_id) REFERENCES roles (id),
        user_tab BOOLEAN,
        user_add BOOLEAN,
        user_view BOOLEAN,
        user_delete BOOLEAN,
        user_edit BOOLEAN,
        role_tab BOOLEAN,
        role_add BOOLEAN,
        role_delete BOOLEAN,
        organization_tab BOOLEAN,
        organization_add BOOLEAN,
        organization_delete BOOLEAN,
        organization_update BOOLEAN,
        office_tab BOOLEAN,
        office_add BOOLEAN,
        office_delete BOOLEAN,
        cat_level_one_tab BOOLEAN,
        cat_level_one_add BOOLEAN,
        cat_level_one_delete BOOLEAN,
        cat_level_one_update BOOLEAN,
        cat_level_two_tab BOOLEAN,
        cat_level_two_add BOOLEAN,
        cat_level_two_delete BOOLEAN,
        cat_level_two_update BOOLEAN,
        cat_level_three_tab BOOLEAN,
        cat_level_three_add BOOLEAN,
        cat_level_three_delete BOOLEAN,
        cat_level_three_update BOOLEAN,
        cat_level_four_tab BOOLEAN,
        cat_level_four_add BOOLEAN,
        cat_level_four_delete BOOLEAN,
        cat_level_four_update BOOLEAN,
        enquiry_field_tab BOOLEAN,
        enquiries_tab BOOLEAN,
        enquiries_add BOOLEAN,
        enquiries_delete BOOLEAN,
        enquiries_edit BOOLEAN,
        enquiries_view BOOLEAN,
        enquiries_assign BOOLEAN,
        follow_up_tab BOOLEAN,
        follow_up_add BOOLEAN,
        follow_up_view BOOLEAN
    );

CREATE TABLE
    products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_name VARCHAR(255) NOT NULL,
        details TEXT NOT NULL,
        type_of_product VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE
    follow_up_products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_id INT,
        follow_up_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE
    enquiry_products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        enquiry_id INT,
        product_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE
    complaint_bd (
        id INT AUTO_INCREMENT PRIMARY KEY,
        enquiry_id INT,
        to_user VARCHAR(255),
        email_subject VARCHAR(255),
        email_body TEXT,
        type_of VARCHAR(255),
        status_request VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

ALTER TABLE `permissions` ADD `followup_report` TINYINT NOT NULL DEFAULT '0' AFTER `customer_tab`,
ADD `enquiry_report` TINYINT NOT NULL DEFAULT '0' AFTER `followup_report`,
ADD `products_tab` TINYINT NOT NULL DEFAULT '0' AFTER `enquiry_report`,
ADD `products_add` TINYINT NOT NULL DEFAULT '0' AFTER `products_tab`,
ADD `products_edit` TINYINT NOT NULL DEFAULT '0' AFTER `products_add`,
ADD `products_delete` TINYINT NOT NULL DEFAULT '0' AFTER `products_edit`;

ALTER TABLE `organization` ADD `currency` VARCHAR(255) NOT NULL DEFAULT 'PKR' AFTER `name`;

ALTER TABLE `products` ADD `price` INT NOT NULL DEFAULT '0' AFTER `created_at`;

ALTER TABLE `smartcrm`.`users` CHANGE COLUMN `cnic` `cnic` VARCHAR(15) NULL,
CHANGE COLUMN `mobile_no` `mobile_no` VARCHAR(15) NULL;

ALTER TABLE enquiries
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE smartcrm.enquiries
ADD COLUMN enquiry_status_steps varchar(255) DEFAULT NULL;

ALTER TABLE `smartcrm`.`follow_up` CHANGE COLUMN `followup_date` `followup_date` DATE NULL DEFAULT NULL;

ALTER TABLE `smartcrm`.`follow_up` CHANGE COLUMN `next_followup_date` `next_followup_date` DATE NULL DEFAULT NULL;


ALTER TABLE `enquiries` ADD `field_eleven` TEXT NULL DEFAULT NULL AFTER `field_ten`, ADD `field_twelve` TEXT NULL DEFAULT NULL AFTER `field_eleven`, ADD `field_thirteen` TEXT NULL DEFAULT NULL AFTER `field_twelve`, ADD `field_fourteen` TEXT NULL DEFAULT NULL AFTER `field_thirteen`, ADD `field_fifteen` TEXT NULL DEFAULT NULL AFTER `field_fourteen`, ADD `field_sixteen` TEXT NULL DEFAULT NULL AFTER `field_fifteen`, ADD `field_seventeen` TEXT NULL DEFAULT NULL AFTER `field_sixteen`, ADD `field_eighteen` TEXT NULL DEFAULT NULL AFTER `field_seventeen`, ADD `field_nineteen` TEXT NULL DEFAULT NULL AFTER `field_eighteen`, ADD `field_twenty` TEXT NULL DEFAULT NULL AFTER `field_nineteen`;
INSERT INTO `enquiry_fields` (`id`, `title`, `field_name`, `status`, `type`, `show_field`, `report_show`, `order_no`) VALUES (NULL, 'field_eleven', 'field_eleven', '1', 'Text', '1', '0', '2800'), (NULL, 'field_twelve', 'field_twelve', '1', 'Text', '1', '0', '2900'), (NULL, 'field_thirteen', 'field_thirteen', '1', 'Text', '1', '0', '3000'), (NULL, 'field_fourteen', 'field_fourteen', '1', 'Text', '1', '0', '3100'), (NULL, 'field_fifteen', 'field_fifteen', '1', 'Text', '1', '0', '3200'), (NULL, 'field_sixteen', 'field_sixteen', '1', 'Text', '1', '0', '3300'), (NULL, 'field_seventeen', 'field_seventeen', '1', 'Text', '1', '0', '3400'), (NULL, 'field_eighteen', 'field_eighteen', '1', 'Text', '1', '0', '3500'), (NULL, 'field_eighteen', 'field_eighteen', '1', 'Text', '1', '0', '3600'), (NULL, 'field_twenty', 'field_twenty', '1', 'Text', '1', '0', '3700');
DROP TABLE enquiry_images;
CREATE TABLE enquiry_images (id int not null primary key auto_increment, image_path TEXT, title TEXT, enquiry_id INT);

ALTER TABLE follow_up ADD next_followup_type VARCHAR(255);

-- ENFO CHANGES TOBE DONE

CREATE TABLE config_lead_stages (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    order_no INT,
    show_on_screen BOOLEAN,
    matured_status BOOLEAN,
    dropped_status BOOLEAN,
    org_id INT,
    FOREIGN KEY (org_id) REFERENCES organization(id)
);
ALTER TABLE organization ADD unique_number BOOLEAN;
ALTER TABLE enquiry_fields ADD disabled BOOLEAN;
ALTER TABLE config_lead_stages ADD set_initial BOOLEAN;

CREATE TABLE first_time_assignment (id int not null primary key auto_increment, date varchar(255), type varchar(255), enquiry_id int, FOREIGN KEY (enquiry_id) REFERENCES enquiries(id));

ALTER TABLE organization ADD date_format VARCHAR(255);

ALTER TABLE `follow_up` 
ADD INDEX `fk_enq_id_idx` (`enquiry_id` ASC) VISIBLE;
;
ALTER TABLE `follow_up` 
ADD CONSTRAINT `fk_enq_id`
  FOREIGN KEY (`enquiry_id`)
  REFERENCES `enquiries` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

  ALTER TABLE `dev_crm`.`assign_enquiries` 
ADD INDEX `fk_enq_aq_id_idx` (`enquiry_id` ASC) VISIBLE,
DROP INDEX `fk_enq_id_idx` ;
;
ALTER TABLE `dev_crm`.`assign_enquiries` 
ADD CONSTRAINT `fk_enq_aq_id`
  FOREIGN KEY (`enquiry_id`)
  REFERENCES `dev_crm`.`enquiries` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

ALTER TABLE `dev_crm`.`assign_enquiries` 
ADD INDEX `fk_user_aq_id_idx` (`user_id` ASC) VISIBLE;
;
ALTER TABLE `dev_crm`.`assign_enquiries` 
ADD CONSTRAINT `fk_user_aq_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `dev_crm`.`users` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

ALTER TABLE `dev_crm`.`follow_up` 
ADD INDEX `fk_user_enq_id_idx` (`user_id` ASC) VISIBLE;
;
ALTER TABLE `dev_crm`.`follow_up` 
ADD CONSTRAINT `fk_user_enq_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `dev_crm`.`users` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

ALTER TABLE `dev_crm`.`follow_up` 
DROP FOREIGN KEY `fk_user_enq_id`;
ALTER TABLE `dev_crm`.`follow_up` 
ADD CONSTRAINT `fk_user_enq_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `dev_crm`.`users` (`id`)
  ON DELETE SET NULL;

ALTER TABLE `dev_crm`.`user_location` 
ADD INDEX `fk_user_loc_id_idx` (`user_id` ASC) VISIBLE;
;
ALTER TABLE `dev_crm`.`user_location` 
ADD CONSTRAINT `fk_user_loc_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `dev_crm`.`users` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;


ALTER TABLE `dev_crm`.`follow_up` 
DROP FOREIGN KEY `fk_user_enq_id`;
ALTER TABLE `dev_crm`.`follow_up` 
ADD CONSTRAINT `fk_user_enq_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `dev_crm`.`users` (`id`)
  ON DELETE CASCADE;



ALTER TABLE follow_up ADD follow_up_type VARCHAR(255);
ALTER TABLE follow_up ADD next_follow_up_type VARCHAR(255);
ALTER TABLE follow_up ADD follow_up_status VARCHAR(255);

ALTER TABLE `dev_crm`.`users` 
;
ALTER TABLE `dev_crm`.`users` ALTER INDEX `fk_office_user_id_idx` VISIBLE;
ALTER TABLE `dev_crm`.`users` 
ADD CONSTRAINT `fk_office_user_id`
  FOREIGN KEY (`office_id`)
  REFERENCES `dev_crm`.`office` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;