---14-02-2025
INSERT INTO role_permissions (role_id, feature_id) VALUES (1, 27);

---10-03-2025
--- *migrating city projects*

DROP TABLE builders;

CREATE TABLE builders (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name TEXT NOT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(255) DEFAULT NULL,
    contact_person_name TEXT DEFAULT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    established_year VARCHAR(50) DEFAULT NULL,
    experience FLOAT DEFAULT 0,
    projects_comp INT(11) DEFAULT 0,
    cities TEXT DEFAULT NULL,
    happy_families INT(11) DEFAULT 0,
    description TEXT DEFAULT NULL,
    rating FLOAT DEFAULT 5,
    logo VARCHAR(100) DEFAULT NULL,
    datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255) DEFAULT NULL,
    updated_by VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `updated-crm-db`.`builders` (
    name,
    status,
    updated_at,
    established_year,
    experience,
    projects_comp,
    cities,
    happy_families,
    description,
    rating,
    logo,
    datetime,
    created_by,
    updated_by
)
SELECT
    b.builder_name AS name,
    CASE b.status
        WHEN 1 THEN 'active'
        ELSE 'inactive'
    END AS status,
    b.updated_at,
    b.established_year,
    b.experience,
    b.projects_comp,
    b.cities,
    b.happy_families,
    b.description,
    b.rating,
    b.logo,
    b.datetime,
    b.created_by,
    b.updated_by
FROM `housiey_laltest_data_entry_13-03-2025`.`builders` AS b;

--- *migrating city projects*
DROP TABLE projects;

CREATE TABLE projects (
    id INT(11) NOT NULL,
    name TEXT NOT NULL,
    location TEXT DEFAULT NULL,
    builder_id INT(11) DEFAULT NULL,
    region_id INT(11) DEFAULT NULL,
    city_id INT(11) DEFAULT NULL,
    status ENUM('active', 'inactive') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    created_by VARCHAR(255) DEFAULT NULL,
    slug TEXT DEFAULT NULL,
    project_title TEXT DEFAULT NULL,
    project_desc TEXT DEFAULT NULL,
    seller_id INT(11) DEFAULT 1006,
    location_id INT(11) NOT NULL DEFAULT 0,
    about TEXT DEFAULT NULL,
    meta_tags TEXT DEFAULT NULL,
    project_type INT(11) DEFAULT NULL,
    unit_type INT(11) DEFAULT NULL,
    land_parcel FLOAT NOT NULL DEFAULT 0,
    no_of_towers INT(11) NOT NULL DEFAULT 0,
    no_of_floors VARCHAR(100) DEFAULT NULL,
    config VARCHAR(200) DEFAULT NULL,
    parking INT(11) DEFAULT NULL,
    target_possession VARCHAR(200) DEFAULT NULL,
    rera_possession VARCHAR(200) DEFAULT NULL,
    area_range_min BIGINT(20) NOT NULL DEFAULT 0,
    area_range_max BIGINT(20) NOT NULL DEFAULT 0,
    saving_amt VARCHAR(200) DEFAULT NULL,
    offer VARCHAR(200) DEFAULT NULL,
    overall_price_range VARCHAR(100) DEFAULT NULL,
    publish_status INT(11) NOT NULL DEFAULT 0,
    create_datetime DATETIME DEFAULT NULL,
    update_datetime DATETIME DEFAULT NULL,
    publish_datetime INT(11) DEFAULT NULL,
    top_launches_status INT(11) NOT NULL DEFAULT 0,
    availability_status VARCHAR(225) DEFAULT 'Available',
    project_main_heading TEXT DEFAULT NULL,
    hide_on_listing TINYINT(1) NOT NULL DEFAULT 0,
    updated_by VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `updated-crm-db`.`projects` (
    id,
    name,
    builder_id,
    status,
    created_at,
    updated_at,
    created_by,
    slug,
    project_title,
    project_desc,
    seller_id,
    location_id,
    about,
    meta_tags,
    project_type,
    unit_type,
    land_parcel,
    no_of_towers,
    no_of_floors,
    config,
    parking,
    target_possession,
    rera_possession,
    area_range_min,
    area_range_max,
    saving_amt,
    offer,
    overall_price_range,
    publish_status,
    create_datetime,
    update_datetime,
    publish_datetime,
    top_launches_status,
    availability_status,
    project_main_heading,
    hide_on_listing,
    updated_by
)
SELECT
    p.id,
    p.project_name,
    p.builder_id,
    CASE p.publish_status
        WHEN 1 THEN 'active'
        ELSE 'inactive'
    END AS status,
    p.created_at,
    p.updated_at,
    p.created_by,
    p.slug,
    p.project_title,
    p.project_desc,
    p.seller_id,
    p.location_id,
    p.about,
    p.meta_tags,
    p.project_type,
    p.unit_type,
    p.land_parcel,
    p.no_of_towers,
    p.no_of_floors,
    p.config,
    p.parking,
    p.target_possession,
    p.rera_possession,
    p.area_range_min,
    p.area_range_max,
    p.saving_amt,
    p.offer,
    p.overall_price_range,
    p.publish_status,
    p.create_datetime,
    p.update_datetime,
    p.publish_datetime,
    p.top_launches_status,
    p.availability_status,
    p.project_main_heading,
    p.hide_on_listing,
    p.updated_by
FROM `housiey_laltest_data_entry_13-03-2025`.`projects` AS p;

--- *migrating city table*

DROP TABLE cities;

CREATE TABLE city (
    id INT(11) NOT NULL,
    name TEXT NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    deleted_at TIMESTAMP DEFAULT current_timestamp(),
    state_id INT(11) NOT NULL DEFAULT 0,
    description TEXT DEFAULT NULL,
    default_city TINYINT(1) NOT NULL DEFAULT 0,
    city_image VARCHAR(225) DEFAULT NULL,
    created_by VARCHAR(255) DEFAULT NULL,
    updated_by VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `updated-crm-db`.`city` (
    id,
    name,
    status,
    created_at,
    updated_at,
    state_id,
    description,
    default_city,
    city_image,
    created_by,
    updated_by
)
SELECT
    c.id,
    c.name,
    CASE c.status
        WHEN 1 THEN 'active'
        ELSE 'inactive'
    END AS status,
    c.created_at,
    c.updated_at,
    c.state_id,
    c.description,
    c.default_city,
    c.city_image,
    c.created_by,
    c.updated_by
FROM `housiey_laltest_data_entry_13-03-2025`.`city` AS c;


CREATE TABLE projects_location_names (
    id INT PRIMARY KEY AUTO_INCREMENT,
    locality_name VARCHAR(255) NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    project_id INT NOT NULL,
    city_id int(11) NOT NULL
);

INSERT INTO projects_location_names (locality_name, project_name, project_id, city_id)
SELECT 
    l.name AS locality_name,
    p.project_name,
    p.id AS project_id,
    l.city_id
FROM 
    project_locations pl
JOIN 
    projects p ON pl.project_id = p.id
JOIN 
    locality l ON pl.locality_id = l.id;


UPDATE user_cities
SET city_id = CASE 
    WHEN city_id = 1 THEN 15
    WHEN city_id = 2 THEN 14
    ELSE city_id
END;

UPDATE region_cities
SET city_id = CASE 
    WHEN city_id = 1 THEN 15
    WHEN city_id = 2 THEN 14
    WHEN city_id = 4 THEN 12
    ELSE city_id
END;

UPDATE launch
SET city_id = CASE 
    WHEN city_id = 1 THEN 15
    WHEN city_id = 2 THEN 14
    ELSE city_id
END;

--missing project

INSERT INTO projects (
    id,
    name,
    location,
    builder_id,
    region_id,
    city_id,
    status,
    created_at,
    updated_at,
    created_by,
    slug,
    project_title,
    project_desc,
    seller_id,
    location_id,
    about,
    meta_tags,
    project_type,
    unit_type,
    land_parcel,
    no_of_towers,
    no_of_floors,
    config,
    parking,
    target_possession,
    rera_possession,
    area_range_min,
    area_range_max,
    saving_amt,
    offer,
    overall_price_range,
    publish_status,
    create_datetime,
    update_datetime,
    publish_datetime,
    top_launches_status,
    availability_status,
    project_main_heading,
    hide_on_listing,
    updated_by
)
VALUES (
    3281,
    'Godrej Greenfront',
    'Hinjewadi',
    158,
    8,
    14,
    'active',
    CURRENT_TIMESTAMP,
    NULL,
    'admin',
    'godrej-greenfront',
    'Godrej Greenfront project title',
    'This is a premium residential project located in the heart of Hinjewadi.',
    1006,
    0,
    'About Godrej Greenfront - an iconic development.',
    'real estate, godrej, hinjewadi, greenfront',
    1,
    2,
    5.5,
    3,
    'G+22',
    '2BHK, 3BHK',
    2,
    'Dec 2025',
    'Mar 2026',
    700,
    1500,
    'Save ₹5 Lakhs',
    'Pre-launch offer available',
    '₹75L - ₹1.5Cr',
    1,
    CURRENT_TIMESTAMP,
    NULL,
    1,
    1,
    'Available',
    'Welcome to Godrej Greenfront – Modern Living Redefined.',
    0,
    'admin'
);

INSERT INTO projects (
    id,
    name,
    location,
    builder_id,
    region_id,
    city_id,
    status,
    created_at,
    updated_at,
    created_by,
    slug,
    project_title,
    project_desc,
    seller_id,
    location_id,
    about,
    meta_tags,
    project_type,
    unit_type,
    land_parcel,
    no_of_towers,
    no_of_floors,
    config,
    parking,
    target_possession,
    rera_possession,
    area_range_min,
    area_range_max,
    saving_amt,
    offer,
    overall_price_range,
    publish_status,
    create_datetime,
    update_datetime,
    publish_datetime,
    top_launches_status,
    availability_status,
    project_main_heading,
    hide_on_listing,
    updated_by
)
VALUES (
    3282,
    'Gera 343 Crest',
    'Kharadi',
    158,
    9,
    14,
    'active',
    CURRENT_TIMESTAMP,
    NULL,
    'admin',
    'godrej-greenfront',
    'Godrej Greenfront project title',
    'This is a premium residential project located in the heart of Hinjewadi.',
    1006,
    0,
    'About Godrej Greenfront - an iconic development.',
    'real estate, godrej, hinjewadi, greenfront',
    1,
    2,
    5.5,
    3,
    'G+22',
    '2BHK, 3BHK',
    2,
    'Dec 2025',
    'Mar 2026',
    700,
    1500,
    'Save ₹5 Lakhs',
    'Pre-launch offer available',
    '₹75L - ₹1.5Cr',
    1,
    CURRENT_TIMESTAMP,
    NULL,
    1,
    1,
    'Available',
    'Welcome to Godrej Greenfront – Modern Living Redefined.',
    0,
    'admin'
);

INSERT INTO projects (
    id,
    name,
    location,
    builder_id,
    region_id,
    city_id,
    status,
    created_at,
    updated_at,
    created_by,
    slug,
    project_title,
    project_desc,
    seller_id,
    location_id,
    about,
    meta_tags,
    project_type,
    unit_type,
    land_parcel,
    no_of_towers,
    no_of_floors,
    config,
    parking,
    target_possession,
    rera_possession,
    area_range_min,
    area_range_max,
    saving_amt,
    offer,
    overall_price_range,
    publish_status,
    create_datetime,
    update_datetime,
    publish_datetime,
    top_launches_status,
    availability_status,
    project_main_heading,
    hide_on_listing,
    updated_by
)
VALUES (
    3283,
    'Kumar Parth Baner',
    'Baner',
    5,
    8,
    14,
    'active',
    CURRENT_TIMESTAMP,
    NULL,
    'admin',
    'godrej-greenfront',
    'Godrej Greenfront project title',
    'This is a premium residential project located in the heart of Hinjewadi.',
    1006,
    0,
    'About Godrej Greenfront - an iconic development.',
    'real estate, godrej, hinjewadi, greenfront',
    1,
    2,
    5.5,
    3,
    'G+22',
    '2BHK, 3BHK',
    2,
    'Dec 2025',
    'Mar 2026',
    700,
    1500,
    'Save ₹5 Lakhs',
    'Pre-launch offer available',
    '₹75L - ₹1.5Cr',
    1,
    CURRENT_TIMESTAMP,
    NULL,
    1,
    1,
    'Available',
    'Welcome to Godrej Greenfront – Modern Living Redefined.',
    0,
    'admin'
);

update projects set location = 'Hinjewadi', region_id=8 where id=1989;
update projects set location = 'Bandra East', region_id=2 where id=1188;
update projects set location = 'Hadapsar', region_id=9 where id=59;
update projects set location = 'Mahalunge', region_id=8 where id=108;
update projects set location = 'Hinjewadi', region_id=8 where id=2421;
update projects set location = 'Hinjewadi', region_id=8 where id=2423;
update projects set location = 'Baner', region_id=8 where id=3197;
update projects set location = 'Mahalunge', region_id=8 where id=486;
update projects set location = 'Hinjewadi', region_id=8 where id=1275;
update projects set location = 'Hinjewadi', region_id=8 where id=397;
update projects set location = 'Hadapsar', region_id=9 where id=2700;
update projects set location = 'Hadapsar', region_id=9 where id=911;
update projects set location = 'Hadapsar', region_id=9 where id=1672;
update projects set location = 'Kharadi', region_id=9 where id=2929;
update projects set location = 'Kharadi', region_id=9 where id=3007;
update projects set location = 'Wakad', region_id=8 where id=3234;
update projects set location = 'Tathawade', region_id=8 where id=965;
update projects set location = 'Punawale', region_id=8 where id=2901;
update projects set location = 'Punawale', region_id=8 where id=2925;
update projects set location = 'Wakad', region_id=8 where id=1006;
update projects set location = 'Kharadi', region_id=9 where id=811;
update projects set location = 'Hinjewadi', region_id=8 where id=1910;



UPDATE launch_leads SET project_id = 1989 WHERE project_id = 10;
UPDATE launch_leads SET project_id = 1188 WHERE project_id = 11;
UPDATE launch_leads SET project_id = 59 WHERE project_id = 12;
UPDATE launch_leads SET project_id = 108 WHERE project_id = 13;
UPDATE launch_leads SET project_id = 2421 WHERE project_id = 14;
UPDATE launch_leads SET project_id = 2423 WHERE project_id = 15;
UPDATE launch_leads SET project_id = 3197 WHERE project_id = 16;
UPDATE launch_leads SET project_id = 486 WHERE project_id = 17;
UPDATE launch_leads SET project_id = 1275 WHERE project_id = 18;
UPDATE launch_leads SET project_id = 397 WHERE project_id = 19;
UPDATE launch_leads SET project_id = 3281 WHERE project_id = 20;
UPDATE launch_leads SET project_id = 2700 WHERE project_id = 21;
UPDATE launch_leads SET project_id = 911 WHERE project_id = 22;
UPDATE launch_leads SET project_id = 1672 WHERE project_id = 23;
UPDATE launch_leads SET project_id = 2929 WHERE project_id = 24;
UPDATE launch_leads SET project_id = 3007 WHERE project_id = 25;
UPDATE launch_leads SET project_id = 3234 WHERE project_id = 26;
UPDATE launch_leads SET project_id = 965 WHERE project_id = 27;
UPDATE launch_leads SET project_id = 2901 WHERE project_id = 28;
UPDATE launch_leads SET project_id = 2925 WHERE project_id = 29;
UPDATE launch_leads SET project_id = 1006 WHERE project_id = 30;
UPDATE launch_leads SET project_id = 811 WHERE project_id = 31;
UPDATE launch_leads SET project_id = 1910 WHERE project_id = 33;
UPDATE launch_leads SET project_id = 3281 WHERE project_id = 20;
UPDATE launch_leads SET project_id = 3282 WHERE project_id = 32;
UPDATE launch_leads SET project_id = 3283 WHERE project_id = 34;

UPDATE launch_projects SET project_id = 1989 WHERE project_id = 10;
UPDATE launch_projects SET project_id = 1188 WHERE project_id = 11;
UPDATE launch_projects SET project_id = 59 WHERE project_id = 12;
UPDATE launch_projects SET project_id = 108 WHERE project_id = 13;
UPDATE launch_projects SET project_id = 2421 WHERE project_id = 14;
UPDATE launch_projects SET project_id = 2423 WHERE project_id = 15;
UPDATE launch_projects SET project_id = 3197 WHERE project_id = 16;
UPDATE launch_projects SET project_id = 486 WHERE project_id = 17;
UPDATE launch_projects SET project_id = 1275 WHERE project_id = 18;
UPDATE launch_projects SET project_id = 397 WHERE project_id = 19;
UPDATE launch_projects SET project_id = 3281 WHERE project_id = 20;
UPDATE launch_projects SET project_id = 2700 WHERE project_id = 21;
UPDATE launch_projects SET project_id = 911 WHERE project_id = 22;
UPDATE launch_projects SET project_id = 1672 WHERE project_id = 23;
UPDATE launch_projects SET project_id = 2929 WHERE project_id = 24;
UPDATE launch_projects SET project_id = 3007 WHERE project_id = 25;
UPDATE launch_projects SET project_id = 3234 WHERE project_id = 26;
UPDATE launch_projects SET project_id = 965 WHERE project_id = 27;
UPDATE launch_projects SET project_id = 2901 WHERE project_id = 28;
UPDATE launch_projects SET project_id = 2925 WHERE project_id = 29;
UPDATE launch_projects SET project_id = 1006 WHERE project_id = 30;
UPDATE launch_projects SET project_id = 811 WHERE project_id = 31;
UPDATE launch_projects SET project_id = 1910 WHERE project_id = 33;
UPDATE launch_projects SET project_id = 3281 WHERE project_id = 20;
UPDATE launch_projects SET project_id = 3282 WHERE project_id = 32;
UPDATE launch_projects SET project_id = 3283 WHERE project_id = 34;

UPDATE lead_assignment SET project_id = 1989 WHERE project_id = 10;
UPDATE lead_assignment SET project_id = 1188 WHERE project_id = 11;
UPDATE lead_assignment SET project_id = 59 WHERE project_id = 12;
UPDATE lead_assignment SET project_id = 108 WHERE project_id = 13;
UPDATE lead_assignment SET project_id = 2421 WHERE project_id = 14;
UPDATE lead_assignment SET project_id = 2423 WHERE project_id = 15;
UPDATE lead_assignment SET project_id = 3197 WHERE project_id = 16;
UPDATE lead_assignment SET project_id = 486 WHERE project_id = 17;
UPDATE lead_assignment SET project_id = 1275 WHERE project_id = 18;
UPDATE lead_assignment SET project_id = 397 WHERE project_id = 19;
UPDATE lead_assignment SET project_id = 3281 WHERE project_id = 20;
UPDATE lead_assignment SET project_id = 2700 WHERE project_id = 21;
UPDATE lead_assignment SET project_id = 911 WHERE project_id = 22;
UPDATE lead_assignment SET project_id = 1672 WHERE project_id = 23;
UPDATE lead_assignment SET project_id = 2929 WHERE project_id = 24;
UPDATE lead_assignment SET project_id = 3007 WHERE project_id = 25;
UPDATE lead_assignment SET project_id = 3234 WHERE project_id = 26;
UPDATE lead_assignment SET project_id = 965 WHERE project_id = 27;
UPDATE lead_assignment SET project_id = 2901 WHERE project_id = 28;
UPDATE lead_assignment SET project_id = 2925 WHERE project_id = 29;
UPDATE lead_assignment SET project_id = 1006 WHERE project_id = 30;
UPDATE lead_assignment SET project_id = 811 WHERE project_id = 31;
UPDATE lead_assignment SET project_id = 1910 WHERE project_id = 33;
UPDATE lead_assignment SET project_id = 3281 WHERE project_id = 20;
UPDATE lead_assignment SET project_id = 3282 WHERE project_id = 32;
UPDATE lead_assignment SET project_id = 3283 WHERE project_id = 34;



UPDATE project_configuration SET project_id = 1989 WHERE project_id = 10;
UPDATE project_configuration SET project_id = 1188 WHERE project_id = 11;
UPDATE project_configuration SET project_id = 59 WHERE project_id = 12;
UPDATE project_configuration SET project_id = 108 WHERE project_id = 13;
UPDATE project_configuration SET project_id = 2421 WHERE project_id = 14;
UPDATE project_configuration SET project_id = 2423 WHERE project_id = 15;
UPDATE project_configuration SET project_id = 3197 WHERE project_id = 16;
UPDATE project_configuration SET project_id = 486 WHERE project_id = 17;
UPDATE project_configuration SET project_id = 1275 WHERE project_id = 18;
UPDATE project_configuration SET project_id = 397 WHERE project_id = 19;
UPDATE project_configuration SET project_id = 3281 WHERE project_id = 20;
UPDATE project_configuration SET project_id = 2700 WHERE project_id = 21;
UPDATE project_configuration SET project_id = 911 WHERE project_id = 22;
UPDATE project_configuration SET project_id = 1672 WHERE project_id = 23;
UPDATE project_configuration SET project_id = 2929 WHERE project_id = 24;
UPDATE project_configuration SET project_id = 3007 WHERE project_id = 25;
UPDATE project_configuration SET project_id = 3234 WHERE project_id = 26;
UPDATE project_configuration SET project_id = 965 WHERE project_id = 27;
UPDATE project_configuration SET project_id = 2901 WHERE project_id = 28;
UPDATE project_configuration SET project_id = 2925 WHERE project_id = 29;
UPDATE project_configuration SET project_id = 1006 WHERE project_id = 30;
UPDATE project_configuration SET project_id = 811 WHERE project_id = 31;
UPDATE project_configuration SET project_id = 1910 WHERE project_id = 33;
UPDATE project_configuration SET project_id = 3281 WHERE project_id = 20;
UPDATE project_configuration SET project_id = 3282 WHERE project_id = 32;
UPDATE project_configuration SET project_id = 3283 WHERE project_id = 34;



UPDATE project_localities SET project_id = 1989 WHERE project_id = 10;
UPDATE project_localities SET project_id = 1188 WHERE project_id = 11;
UPDATE project_localities SET project_id = 59 WHERE project_id = 12;
UPDATE project_localities SET project_id = 108 WHERE project_id = 13;
UPDATE project_localities SET project_id = 2421 WHERE project_id = 14;
UPDATE project_localities SET project_id = 2423 WHERE project_id = 15;
UPDATE project_localities SET project_id = 3197 WHERE project_id = 16;
UPDATE project_localities SET project_id = 486 WHERE project_id = 17;
UPDATE project_localities SET project_id = 1275 WHERE project_id = 18;
UPDATE project_localities SET project_id = 397 WHERE project_id = 19;
UPDATE project_localities SET project_id = 3281 WHERE project_id = 20;
UPDATE project_localities SET project_id = 2700 WHERE project_id = 21;
UPDATE project_localities SET project_id = 911 WHERE project_id = 22;
UPDATE project_localities SET project_id = 1672 WHERE project_id = 23;
UPDATE project_localities SET project_id = 2929 WHERE project_id = 24;
UPDATE project_localities SET project_id = 3007 WHERE project_id = 25;
UPDATE project_localities SET project_id = 3234 WHERE project_id = 26;
UPDATE project_localities SET project_id = 965 WHERE project_id = 27;
UPDATE project_localities SET project_id = 2901 WHERE project_id = 28;
UPDATE project_localities SET project_id = 2925 WHERE project_id = 29;
UPDATE project_localities SET project_id = 1006 WHERE project_id = 30;
UPDATE project_localities SET project_id = 811 WHERE project_id = 31;
UPDATE project_localities SET project_id = 1910 WHERE project_id = 33;
UPDATE project_localities SET project_id = 3281 WHERE project_id = 20;
UPDATE project_localities SET project_id = 3282 WHERE project_id = 32;
UPDATE project_localities SET project_id = 3283 WHERE project_id = 34;



UPDATE project_marketing_channels SET project_id = 1989 WHERE project_id = 10;
UPDATE project_marketing_channels SET project_id = 1188 WHERE project_id = 11;
UPDATE project_marketing_channels SET project_id = 59 WHERE project_id = 12;
UPDATE project_marketing_channels SET project_id = 108 WHERE project_id = 13;
UPDATE project_marketing_channels SET project_id = 2421 WHERE project_id = 14;
UPDATE project_marketing_channels SET project_id = 2423 WHERE project_id = 15;
UPDATE project_marketing_channels SET project_id = 3197 WHERE project_id = 16;
UPDATE project_marketing_channels SET project_id = 486 WHERE project_id = 17;
UPDATE project_marketing_channels SET project_id = 1275 WHERE project_id = 18;
UPDATE project_marketing_channels SET project_id = 397 WHERE project_id = 19;
UPDATE project_marketing_channels SET project_id = 3281 WHERE project_id = 20;
UPDATE project_marketing_channels SET project_id = 2700 WHERE project_id = 21;
UPDATE project_marketing_channels SET project_id = 911 WHERE project_id = 22;
UPDATE project_marketing_channels SET project_id = 1672 WHERE project_id = 23;
UPDATE project_marketing_channels SET project_id = 2929 WHERE project_id = 24;
UPDATE project_marketing_channels SET project_id = 3007 WHERE project_id = 25;
UPDATE project_marketing_channels SET project_id = 3234 WHERE project_id = 26;
UPDATE project_marketing_channels SET project_id = 965 WHERE project_id = 27;
UPDATE project_marketing_channels SET project_id = 2901 WHERE project_id = 28;
UPDATE project_marketing_channels SET project_id = 2925 WHERE project_id = 29;
UPDATE project_marketing_channels SET project_id = 1006 WHERE project_id = 30;
UPDATE project_marketing_channels SET project_id = 811 WHERE project_id = 31;
UPDATE project_marketing_channels SET project_id = 1910 WHERE project_id = 33;
UPDATE project_marketing_channels SET project_id = 3281 WHERE project_id = 20;
UPDATE project_marketing_channels SET project_id = 3282 WHERE project_id = 32;
UPDATE project_marketing_channels SET project_id = 3283 WHERE project_id = 34;

UPDATE `updated-crm-db`.projects p
JOIN `housiey_laltest_data_entry_13-03-2025`.projects_location_names pln 
    ON p.id = pln.project_id
SET 
    p.location = pln.locality_name,
    p.city_id = pln.city_id;



Gera Joy On Treetop --> 10 --> 1989
Raymond Bandra --> 11 --> 1188
Joyville Hadapsar --> 12 --> 59
Godrej Green Vistas --> 13 --> 108
VTP Volare --> 14 --> 2421
VTP Monarque --> 15 --> 2423
VTP Vibrance --> 16 --> 3197
Itrend Futura --> 17 --> 486
Lodha Magnus --> 18 --> 1275
Lodha Panache --> 19 --> 397
Godrej Greenfront --> 20 --> 3281
The Premia --> 21 --> 2700
Kumar Magnacity --> 22 --> 911
Holystico Astrana --> 23 --> 1672
Mahindra Ivylush --> 24 --> 2929
VTP Cygnus 2 --> 25 --> 3007
Lodha Altero --> 26 --> 3234
Rohan Harita --> 27 --> 965
Legacy Milestone --> 28 --> 2901
Millennium Samriddhi --> 29 --> 2925

VJ Yashwin Urbo Centro --> 30 --> 1006 region id = 8
Gera Island Of Joy --> 31 --> 811 --> region =9
Kohinoor Central Park --> 33 --> 1910 region id 8

--- update region ids for pune and mumbai
UPDATE projects
SET region_id = CASE 
                   WHEN city_id = 14 THEN 9
                   WHEN city_id = 15 THEN 7
               END
WHERE city_id IN (14, 15)
  AND id NOT IN (
      1989, 1188, 59, 108, 2421, 2423, 3197, 486, 1275, 397,
      2700, 911, 1672, 2929, 3007, 3234, 965, 2901, 2925,
      1006, 811, 1910
  );

--- updating region ids for other projects except mumbai and pune
UPDATE projects
SET region_id = 4
WHERE city_id NOT IN (14, 15)
  AND id NOT IN (
      1989, 1188, 59, 108, 2421, 2423, 3197, 486, 1275, 397,
      2700, 911, 1672, 2929, 3007, 3234, 965, 2901, 2925,
      1006, 811, 1910
  );


--- project configuration part
DROP TABLE configurations;

CREATE TABLE configurations (
    id INT(11) NOT NULL,
    name TEXT NOT NULL,
    status ENUM('active', 'inactive') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    created_by VARCHAR(255) DEFAULT NULL,
    updated_by VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id)
);

INSERT INTO update_crm_new_db.configurations (id, name)
SELECT id, name
FROM housiey_db_19_03_2025.configs;

update project_configuration set configuration_id = 8 where configuration_id = 2;
update project_configuration set configuration_id = 14 where configuration_id = 3;
update project_configuration set configuration_id = 105 where configuration_id = 8;
update project_configuration set configuration_id = 33 where configuration_id = 9;
update project_configuration set configuration_id = 17 where configuration_id = 4;
update project_configuration set configuration_id = 119 where configuration_id = 10;
update project_configuration set configuration_id = 156 where configuration_id = 11;
update project_configuration set configuration_id = 25 where configuration_id = 5;
update project_configuration set configuration_id = 2 where configuration_id = 12;

CREATE TABLE project_config_info (
    project_id INT(11) NOT NULL,
    configuration_id INT(11) NOT NULL,
    configuration_name TEXT NOT NULL,
    ticket_size DECIMAL(15,2) DEFAULT NULL
);

INSERT INTO project_config_info (project_id, configuration_id, configuration_name, ticket_size)
SELECT 
    pc.project_id,
    pc.config_id AS configuration_id,
    c.name AS configuration_name,
    pc.price AS ticket_size
FROM 
    project_configs pc
JOIN 
    configs c ON pc.config_id = c.id;

INSERT INTO update_crm_new_db.project_configuration (project_id, configuration_id,configuration_name,ticket_size)
SELECT DISTINCT 
    project_id,
    configuration_id,
    configuration_name,
    ticket_size
FROM housiey_db_19_03_2025.project_config_info
ON DUPLICATE KEY UPDATE configuration_name = VALUES(configuration_name);


-- 13-04-2025
ALTER TABLE users DROP INDEX email;

UPDATE users SET email='suresh.hela@housiey.com', password ='$2a$10$LaQoHGdw.Iji/aIdpj/GquQRh2A4i6iAZhN3iu.rcbVnXgMXsqRHa'
-- 21.04.2025
SELECT * FROM `lead_detail` WHERE phone_number IS NOT NULL AND phone_number REGEXP '^[0-9]{10}$' AND phone_number NOT LIKE '+91%';
UPDATE lead_detail SET phone_number = CONCAT('+91', phone_number) WHERE phone_number REGEXP '^[0-9]{10}$' AND phone_number NOT LIKE '+91%';

SELECT * FROM `lead_detail` WHERE alternate_number IS NOT NULL AND alternate_number REGEXP '^[0-9]{10}$' AND alternate_number NOT LIKE '+91%';
UPDATE lead_detail SET alternate_number = CONCAT('+91', alternate_number) WHERE alternate_number REGEXP '^[0-9]{10}$' AND alternate_number NOT LIKE '+91%';

SELECT * FROM `lead_detail` WHERE whatsApp_number IS NOT NULL AND whatsApp_number REGEXP '^[0-9]{10}$' AND whatsApp_number NOT LIKE '+91%';
UPDATE lead_detail SET whatsApp_number = CONCAT('+91', whatsApp_number) WHERE whatsApp_number REGEXP '^[0-9]{10}$' AND whatsApp_number NOT LIKE '+91%';

SELECT * FROM `lead_detail` WHERE additional_number IS NOT NULL AND additional_number REGEXP '^[0-9]{10}$' AND additional_number NOT LIKE '+91%';
UPDATE lead_detail SET additional_number = CONCAT('+91', additional_number) WHERE additional_number REGEXP '^[0-9]{10}$' AND additional_number NOT LIKE '+91%';

-- 22.04.2025 (country column add in lead_detail Table)
ALTER TABLE `lead_detail` ADD `country` VARCHAR(250) NULL DEFAULT NULL AFTER `additional_number`;




---17-04-2025
ALTER TABLE roles ADD COLUMN manager_role_id INT NULL, ADD COLUMN subordinate_role_id TEXT NULL, ADD COLUMN city_id TEXT NULL;

UPDATE roles SET city_id = ( SELECT GROUP_CONCAT(id) FROM city );

ALTER TABLE roles
ADD COLUMN `sort_order` INT NOT NULL;

update roles set `sort_order` = 0 where name like '%human_resources%';
update roles set `sort_order` = 1 where name like '%super_admin%';
update roles set `sort_order` = 2 where name like '%city_head%';
update roles set `sort_order` = 3 where name like '%cluster_manager%';
update roles set `sort_order` = 4 where name like '%team_lead%';
update roles set `sort_order` = 5 where name like '%field_sales%';
update roles set `sort_order` = 6 where name like '%pre_sales%';

---21-04-2025
UPDATE roles set subordinate_role_id = '13' WHERE id = 1;
UPDATE roles set manager_role_id = 1 WHERE id = 15;
UPDATE roles set manager_role_id = 1, subordinate_role_id = '4' WHERE id = 13;
update roles set manager_role_id = 13, subordinate_role_id = '25' where id = 4;
update roles set manager_role_id = 4, subordinate_role_id = '12,10' where id  = 25;
update roles set manager_role_id = 25 where id IN (12,10);

---23-04-2025 for reporting_manager_id

ALTER TABLE launch ADD reporting_manager_ids JSON NULL DEFAULT NULL AFTER team_head_id;

UPDATE launch l JOIN user_role cm_ur ON l.cluster_manager_id = cm_ur.user_id JOIN roles cm_roles ON cm_ur.role_id = cm_roles.id JOIN user_role th_ur ON l.team_head_id = th_ur.user_id JOIN roles th_roles ON th_ur.role_id = th_roles.id LEFT JOIN users city_head ON city_head.id = l.cluster_manager_id SET l.reporting_manager_ids = JSON_OBJECT( 'city_head_id', city_head.reporting_manager_id, CONCAT(LOWER(REPLACE(cm_roles.name, ' ', '')), '_id'), l.cluster_manager_id, CONCAT(LOWER(REPLACE(th_roles.name, ' ', '')), '_id'), l.team_head_id ) WHERE city_head.id IS NOT NULL;


---02-05-2025
DELETE FROM roles WHERE `roles`.`id` = 71;


--06-05-2025

UPDATE `user_designation` SET `designation_id` = '14' WHERE `user_designation`.`id` = 36;
UPDATE `user_role` SET `role_id` = '25' WHERE `user_role`.`id` = 261;
UPDATE `users` SET `reporting_manager_id` = '254' WHERE `users`.`id` = 377;
UPDATE `users` SET `reporting_manager_id` = '255' WHERE `users`.`id` = 361;
UPDATE `users` SET `reporting_manager_id` = '257' WHERE `users`.`id` = 258;
UPDATE `users` SET `reporting_manager_id` = '254' WHERE `users`.`id` = 372;

--13-05-2025

ALTER TABLE `launch` DROP `cluster_manager_id`, DROP `team_head_id`;