init.sql

DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'root') THEN
        CREATE ROLE root WITH LOGIN PASSWORD 'root';
    END IF;
END
$$;

-- drop
DO $$ 
DECLARE 
    r RECORD;
BEGIN
    -- Drop all tables
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
    
    -- Drop all types
    FOR r IN (
        SELECT typname 
        FROM pg_type 
        WHERE typnamespace = 'public'::regnamespace 
          AND typtype = 'e'  -- Only drop ENUM types
    ) LOOP
        EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(r.typname) || ' CASCADE';
    END LOOP;
END $$;

-- extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- type
DO $$
BEGIN
    -- role_name type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'role_name'
    ) THEN
        CREATE TYPE role_name AS ENUM (
            'ROLE_TSL',
            'ROLE_DIRECTOR', 
            'ROLE_ADMIN', 
            'ROLE_ENVIRONMENT_OFFICER', 
            'ROLE_MANAGEMENT', 
            'ROLE_THIRD_PARTY'
        );
    END IF;

    -- company_unit_type type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'company_unit_type'
    ) THEN
        CREATE TYPE company_unit_type AS ENUM ('MAIN', 'SUB');
    END IF;

    -- user_status type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'user_status'
    ) THEN
        CREATE TYPE user_status AS ENUM (
            'ACTIVE', 
            'INACTIVE', 
            'SUSPENDED', 
            'PENDING', 
            'IN_REVIEW', 
            'APPROVED',
            'APPROVED_WO_INDUSTRY', 
            'APPROVED_WO_INDUSTRY_AND_MAIN_UNIT', 
            'REJECTED', 
            'CORRECTION'
        );
    END IF;

    -- unit_type type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'unit_type'
    ) THEN
        CREATE TYPE unit_type AS ENUM ('length', 'weight', 'count', 'other');
    END IF;

    -- resource_type type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'resource_type'
    ) THEN
        CREATE TYPE resource_type AS ENUM (
            'raw_material', 
            'product', 
            'byproduct', 
            'fuel',
            'waste',
            'other'
        );
    END IF;

    -- transaction_type type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'transaction_type'
    ) THEN
        CREATE TYPE transaction_type AS ENUM ('IN', 'OUT');
    END IF;

    -- designation_type type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'designation_type'
    ) THEN
        CREATE TYPE designation_type AS ENUM (
            'TSL', 
            'Administrator', 
            'Manager', 
            'Director', 
            'Environment Officer', 
            'Third Party'
        );
    END IF;

    -- email_status type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'email_status'
    ) THEN
        CREATE TYPE email_status AS ENUM (
            'VERIFIED', 
            'NOT_VERIFIED', 
            'PENDING'
        );
    END IF;

    -- form_status type
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_type 
        WHERE typname = 'form_status'
    ) THEN
        CREATE TYPE form_status AS ENUM (
            'incomplete', 
            'complete', 
            'pending', 
            'under review', 
            'approved', 
            'rejected'
        );
    END IF;
END $$;

-- tables
CREATE TABLE role (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name role_name UNIQUE
);

CREATE TABLE email(
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email_id VARCHAR(255) NOT NULL,
    email_status email_status DEFAULT 'NOT_VERIFIED'
);

CREATE TABLE address (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    street VARCHAR(255),
    line2 VARCHAR(255),
    line3 VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    district VARCHAR(255),
    country VARCHAR(255),
    pincode VARCHAR(20),
    village VARCHAR(255),
    taluka VARCHAR(255),
    plot_number VARCHAR(255),
    ro VARCHAR(255),
    sro VARCHAR(255)
);

CREATE TABLE contact_person (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    designation VARCHAR(255),
    phone VARCHAR(255),
    email_id UUID,
    FOREIGN KEY (email_id) REFERENCES email(id)
);

CREATE TABLE industry_category (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) UNIQUE
);

CREATE TABLE industry_scale (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) UNIQUE
);

CREATE TABLE industry_type (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(1024) UNIQUE
);

CREATE TABLE industry_link (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    industry_scale_id UUID,
    industry_type_id UUID,
    industry_category_id UUID,
    FOREIGN KEY (industry_category_id) REFERENCES industry_category(id) ON DELETE SET NULL,
    FOREIGN KEY (industry_scale_id) REFERENCES industry_scale(id) ON DELETE SET NULL,
    FOREIGN KEY (industry_type_id) REFERENCES industry_type(id) ON DELETE SET NULL
);

CREATE TABLE company_profile (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    contact_person_id UUID,
    name VARCHAR(255),
    fax VARCHAR(255),
    phone_number VARCHAR(255),
    website VARCHAR(255),
    established_at DATE,
    registration_number VARCHAR(50),
    address_id UUID,
    email_id UUID,
    FOREIGN KEY (email_id) REFERENCES email(id),
    FOREIGN KEY (address_id) REFERENCES address(id) ON DELETE SET NULL, 
    FOREIGN KEY (contact_person_id) REFERENCES contact_person(id)
);

CREATE TABLE company_unit (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_profile_id UUID,
    address_id UUID,
    industry_link_id UUID,
    name VARCHAR(50),
    type company_unit_type,
    unit_fax VARCHAR(255),
    unit_phone_number VARCHAR(255),
    work_day INT,
    working_hour INT,
    email_id UUID,
    FOREIGN KEY (email_id) REFERENCES email(id),
    FOREIGN KEY (address_id) REFERENCES address(id) ON DELETE SET NULL,
    FOREIGN KEY (company_profile_id) REFERENCES company_profile(id) ON DELETE CASCADE,
    FOREIGN KEY (industry_link_id) REFERENCES industry_link(id) ON DELETE SET NULL
);

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email_id UUID,
    phone VARCHAR(20),
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    enabled BOOLEAN,
    designation designation_type,
    company_unit_id UUID,
    failed_login_count INT CHECK (failed_login_count >= 0),
    last_login_date TIMESTAMPTZ,
    locked BOOLEAN,
    status user_status DEFAULT 'INACTIVE',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (email_id) REFERENCES email(id),
    FOREIGN KEY (company_unit_id) REFERENCES company_unit(id) ON DELETE SET NULL
);

CREATE TABLE user_role (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    UNIQUE (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE
);

CREATE TABLE employee (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_id UUID,
    user_id UUID NOT NULL,
    contact_person_id UUID,
    name VARCHAR(500),
    gender VARCHAR(20),
    date_of_birth DATE,
    form_status form_status DEFAULT 'incomplete',
    FOREIGN KEY (company_id) REFERENCES company_profile(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (contact_person_id) REFERENCES contact_person(id)
);

CREATE TABLE email_template (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL UNIQUE,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL
);

CREATE TABLE email_outbox (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recipient_email VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    sent_date TIMESTAMPTZ DEFAULT NOW(),
    status VARCHAR(16),
    email_id UUID,
    FOREIGN KEY (email_id) REFERENCES email(id)
);

CREATE TABLE unit (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(50),
    symbol VARCHAR(10),
    unit_type unit_type,
    conversion_factor NUMERIC(10, 5)
);

CREATE TABLE resource (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    unit_id UUID,
    name VARCHAR(255) NOT NULL,
    quantity NUMERIC(10, 2),
    resource_type resource_type,
    FOREIGN KEY (unit_id) REFERENCES unit(id)
);

CREATE TABLE resource_transaction (
    id UUID NOT NULL,
    company_unit_id UUID NOT NULL,
    resource_id UUID NOT NULL,
    transaction_type transaction_type NOT NULL,
    quantity NUMERIC(10, 2) NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    FOREIGN KEY (resource_id) REFERENCES resource(id) ON DELETE CASCADE
) PARTITION BY LIST (company_unit_id);

CREATE TABLE notification_type (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(50) UNIQUE,
    description TEXT,
    type VARCHAR(50),
    priority INT
);

CREATE TABLE notification (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    from_user UUID,
    message TEXT,
    notification_type UUID NOT NULL,
    pinned BOOLEAN DEFAULT FALSE,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    trigger_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_user) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (notification_type) REFERENCES notification_type(id) ON DELETE CASCADE
);

-- partitions
CREATE OR REPLACE FUNCTION create_resource_transaction_partition()
RETURNS TRIGGER AS $$
DECLARE
    partition_name TEXT;
    partition_exists BOOLEAN;
BEGIN
    partition_name := 'resource_transaction_' || NEW.id::text;
    
    -- Check if the partition already exists
    SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = current_schema()
        AND table_name = partition_name
    ) INTO partition_exists;

    -- If the partition doesn't exist, create it
    IF NOT partition_exists THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF resource_transaction FOR VALUES IN (%L)', partition_name, NEW.id);
        EXECUTE format('ALTER TABLE %I ADD PRIMARY KEY (id)', partition_name);
        EXECUTE format('CREATE INDEX ON %I (timestamp)', partition_name);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- triggers
CREATE TRIGGER create_resource_transaction_partition_trigger
AFTER INSERT ON company_unit
FOR EACH ROW
EXECUTE FUNCTION create_resource_transaction_partition();

-- data
INSERT INTO email (id, email_id) VALUES
    (uuid_generate_v4(), 'tsl@tsl.com'),
    (uuid_generate_v4(), 'admin1@tsl.com'),
    (uuid_generate_v4(), 'director1@tsl.com'),
    (uuid_generate_v4(), '1env1@tsl.com'),
    (uuid_generate_v4(), '1man1@tsl.com'),
    (uuid_generate_v4(), '1thp1@tsl.com'),
    (uuid_generate_v4(), '2env1@tsl.com'),
    (uuid_generate_v4(), '2man1@tsl.com'),
    (uuid_generate_v4(), '2thp1@tsl.com'),
    (uuid_generate_v4(), '3env1@tsl.com'),
    (uuid_generate_v4(), '3man1@tsl.com'),
    (uuid_generate_v4(), '3thp1@tsl.com'),
    (uuid_generate_v4(), 'admin2@tsl.com'),
    (uuid_generate_v4(), 'director2@tsl.com'),
    (uuid_generate_v4(), '1man2@tsl.com'),
    (uuid_generate_v4(), '1thp2@tsl.com'),
    (uuid_generate_v4(), '1env2@tsl.com'),
    (uuid_generate_v4(), '2man2@tsl.com'),
    (uuid_generate_v4(), '2thp2@tsl.com'),
    (uuid_generate_v4(), '2env2@tsl.com'),
    (uuid_generate_v4(), 'it@techknowgreen.com'),
    (uuid_generate_v4(), 'it@techknowblue.com'),
    (uuid_generate_v4(), 'mainunit@techknowgreen.com'),
    (uuid_generate_v4(), 'subunit1@techknowgreen.com'),
    (uuid_generate_v4(), 'subunit2@techknowgreen.com'),
    (uuid_generate_v4(), 'mainunit@techknowblue.com'),
    (uuid_generate_v4(), 'subunit1@techknowblue.com'),
    (uuid_generate_v4(), 'john.doe@example.com'),
    (uuid_generate_v4(), 'example@example.com');


INSERT INTO contact_person (id, name, designation, phone, email_id)
VALUES 
    (uuid_generate_v4(), 'Ajay Ojha', 'MD', '9876543212', (SELECT id FROM email WHERE email_id = 'example@example.com')),
    (uuid_generate_v4(), 'Prasad Pawar', 'CEO', '9876543212', (SELECT id FROM email WHERE email_id = 'example@example.com')),
    (uuid_generate_v4(), 'Ritesh Gujar', 'IT', '9876543212', (SELECT id FROM email WHERE email_id = 'example@example.com'));


INSERT INTO role (name) VALUES
    ('ROLE_TSL'),
    ('ROLE_DIRECTOR'),
    ('ROLE_ADMIN'),
    ('ROLE_ENVIRONMENT_OFFICER'),
    ('ROLE_MANAGEMENT'),
    ('ROLE_THIRD_PARTY');

INSERT INTO industry_category (name) VALUES
    ('GREEN'),
    ('ORANGE'),
    ('RED'),
    ('WHITE');

INSERT INTO industry_scale (name) VALUES
    ('LSI'),
    ('MSI'),
    ('SSI');


INSERT INTO industry_type (name) VALUES
    ('Local Body'),
    ('G95 CBG plants based on crop residue (paddy straw /wheat straw /corn sweet sorghum /napier grass, etc.)'),
    ('O93 Without any surface treatment activity such as washing, metal surface finishing operations, pickling, plating, electroplating, phosphating & without heat treatment'),
    ('G64 Railway Stations(Waste Water Generation < 10 KLD)'),
    ('G65 Gaushalas'),
    ('G24 Heat treatment with any of the new technology like ultrasound probe, induction hardening, ionization beam, gas carburizing etc.'),
    ('G40 Rolling mill (gas fired) and cold rolling mill'),
    ('G01 Aluminium utensils from aluminium circles.'),
    ('G2 Ayurvedic and homeopathic medicines (without boiler)'),
    ('G3 Bakery/confectionery/sweets products (with production capacity <1tpd (with gas or electrical oven))'),
    ('G4 Bi-axially oriented PP film along with metalizing operations'),
    ('G5 Biomass briquettes (sun drying) without using toxic hazardous wastes'),
    ('G6 Blending of melamine resins & different powder, additives by physical mixing'),
    ('G7 Brass and bell metal utensils manufacturing from circles(dry mechanical operation without re-rolling facility)'),
    ('G8 Candy'),
    ('G9 Cardboard or corrugated box and paper products (excluding paper or pulp manufacturing and without using boilers)'),
    ('G10 Carpentry & wooden furniture manufacturing (excluding saw mill) with the help of electrical (motorized) machines such as electrical wood planner, steel saw cutting circular blade, etc.'),
    ('G11 Cement products (without using asbestos / boiler / steam curing) like pipe, pillar, jafri, well ring, block/tiles etc.(should be done in closed covered shed to control fugitive emissions)'),
    ('G12 Ceramic colour manufacturing by mixing & blending only (not using boiler and wastewater recycling process)'),
    ('G13 Chilling plant, cold storage and ice making'),
    ('G14 Coke briquetting (sun drying)'),
    ('G15 Cotton spinning and weaving (small scale)'),
    ('G16 Dal Mills'),
    ('G17 Decoration of ceramic cups and plates by electric furnace'),
    ('G18 Digital printing on PVC clothes'),
    ('G19 Facility of handling, storage and transportation of food grains in bulk'),
    ('G20 Flour mills (dry process)'),
    ('G21 Glass, ceramic, earthen potteries, tile and tile manufacturing using electrical kiln or not involving fossil fuel kiln'),
    ('G22 Glue from starch (physical mixing) with gas / electrically operated oven /boiler'),
    ('G23 Gold and silver smithy (purification with acid smelting operation and sulphuric acid polishing operation) (using less or equal to 1 litre of sulphuric acid/ nitric acid per month)'),
    ('G25 Insulation and other coated papers (excluding paper or pipe manufacturing)'),
    ('G26 Leather footwear and leather products (excluding tanning and hide processing except cottage scale)'),
    ('G27 Lubricating oil, greases or petroleum based products (only blending at normal temperature)'),
    ('G28 Manufacturing of pasted veneers using gas fired boiler or thermic fluid heater and by sun drying'),
    ('G29 Oil mill Ghani and extraction (no hydrogenation / refining)'),
    ('G30 Packing materials manufacturing from non asbestos fibre, vegetable fibre yarn'),
    ('G31 Phenyl/toilet cleaner formulation and bottling'),
    ('G32 Polythene and plastic processed products manufacturing (virgin plastic)'),
    ('G33 Poultry, Hatchery and Piggery'),
    ('G34 Power looms (without dye and bleaching)'),
    ('G35 Puffed rice (muri) (using gas or electrical heating system)'),
    ('G36 Pulverization of bamboo and scrap wood'),
    ('G38 Reprocessing of waste cotton'),
    ('G39 Rice mill (Rice hullers only)'),
    ('G41 Rubber goods industry (with gas operated baby)'),
    ('G42 Saw mills'),
    ('G43 Soap manufacturing (hand made without steam boiling/ boiler)'),
    ('G44 Spice grinding (upto-20 HP motor)'),
    ('G45 Spice grinding (>20 hp motor)'),
    ('G46 Steel furniture without spray painting'),
    ('G47 Steeping and processing of grains'),
    ('G48 Tyres and tube retreating (without boilers)'),
    ('G49 Chilling plant and ice making without using ammonia'),
    ('G50 CO2 recovery'),
    ('G51 Distilled water (without boiler) with electricity as source of heat'),
    ('G52 Hotels (up to 20 rooms and without boilers)'),
    ('G53 Manufacturing of optical lenses (using electrical furnace)'),
    ('G54 Mineralized water'),
    ('G55 Tamarind powder manufacturing'),
    ('G56 Cutting, sizing and polishing of marble stone'),
    ('G57 Emery powder (fine dust of sand) manufacturing'),
    ('G58 Flyash export, transport & disposal facilities'),
    ('G59 Mineral stack yard / Railway sidings'),
    ('G60 Oil and gas transportation pipeline'),
    ('G61 Seasoning of wood in steam heated chamber'),
    ('G62 Synthetic detergent formulation'),
    ('G63 Tea processing (with boiler)'),
    ('O1 Dismantling of rolling stocks (wagons/ coaches)'),
    ('O2 Bakery and confectionery units with production capacity > 1 TPD. (With ovens / furnaces)'),
    ('O3 Chanachur and ladoo from puffed and beaten rice (muri and shira) using husk fired oven'),
    ('O4 Coated electrode manufacturing'),
    ('O5 Compact disc computer floppy and cassette manufacturing / Reel manufacturing'),
    ('O6 Flakes from rejected PET bottle'),
    ('O7 Food and food processing including fruits and vegetable processing'),
    ('O8 Jute processing without dyeing'),
    ('O9 Manufacturing of silica gel'),
    ('O10 Manufacturing of tooth powder, toothpaste, talcum powder and other cosmetic items'),
    ('O11 Printing or etching of glass sheet using hydrofluoric acid'),
    ('O12 Silk screen printing, sari printing by wooden blocks'),
    ('O13 Synthetic detergents and soaps (excluding formulation)'),
    ('O15 Cotton spinning and weaving (medium and large scale)'),
    ('O16 Almirah, Grill Manufacturing (Dry Mechanical Process)'),
    ('O17 Aluminium & copper extraction from scrap using oil fired furnace (dry process only)'),
    ('O18 Automobile servicing, repairing and painting (excluding only fuel dispensing)'),
    ('O19 Ayurvedic and homeopathic medicine'),
    ('O20 Brickfields (excluding fly ash brick manufacturing using lime process)'),
    ('O21 Building and construction project more than 20,000 sq. m built up area'),
    ('O22 Ceramics and Refractories'),
    ('O23 Coal washeries'),
    ('O24 Dairy and dairy products (small scale)'),
    ('O25 DG set of capacity >1MVA but < 5MVA'),
    ('O26 Dry coal processing, mineral processing, industries involving ore sintering, pelletizing, grinding & pulverization'),
    ('O27 Fermentation industry including manufacture of yeast, beer, distillation of alcohol (Extra Neutral Alcohol)'),
    ('O28 Ferrous and Non-ferrous metal extraction involving different furnaces through melting, refining, re-processing, casting and alloy-making'),
    ('O29 Fertilizer (granulation / formulation / blending only)'),
    ('O30 Fish feed, poultry feed and cattle feed'),
    ('O31 Fish processing and packing (excluding chilling of fishes)'),
    ('O32 Forging of ferrous and non-ferrous metals (using oil and gas fired furnaces)'),
    ('O33 Formulation/pelletization of camphor tablets, naphthalene balls from camphor/ naphthalene powders.'),
    ('O34 Glass ceramics, earthen potteries and tile manufacturing using oil and gas fired kilns, coating on glasses using cerium fluorides and magnesium fluoride etc.'),
    ('O35 Gravure printing, digital printing on flex, vinyl'),
    ('O37 Hot mix plants'),
    ('O38 Hotels (< 3 star) or hotels having > 20 rooms and less than 100 rooms'),
    ('O39 Ice cream'),
    ('O42 Industry or processes involving foundry operations'),
    ('O43 Lime manufacturing (using lime kiln)'),
    ('O44 Liquid floor cleaner, black phenyl, liquid soap, glycerol mono-stearate manufacturing'),
    ('O45 Manufacturing of glass'),
    ('O46 Manufacturing of iodized salt from crude/raw salt'),
    ('O47 Manufacturing of mirror from sheet glass'),
    ('O48 Manufacturing of mosquito repellent coil'),
    ('O49 Manufacturing of Starch/Sago'),
    ('O50 Mechanized laundry using oil fired boiler'),
    ('O51 Modular wooden furniture from particle board, MDF < swan timber etc, Ceiling tiles/partition board from saw dust, wood chips etc., and other agricultural waste using synthetic adhesive resin, wooden box making (With boiler)'),
    ('O52 New highway construction project'),
    ('O53 Non-alcoholic beverages(soft drink) & bottling of alcohol/non alcoholic products'),
    ('O54 Paint blending and mixing (Ball mill)'),
    ('O55 Paints and varnishes (mixing and blending)'),
    ('O56 Ply-board manufacturing (including Veneer and laminate) with oil fired boiler/ thermic fluid heater(without resin plant)'),
    ('O57 Potable alcohol (IMFL) by blending, bottling of alcohol products'),
    ('O58 Printing ink manufacturing'),
    ('O60 Reprocessing of waste plastic including PVC'),
    ('O62 Spray painting, paint baking, paint shipping'),
    ('O63 Steel and steel products using various furnaces like blast furnace/open hearth furnace/induction furnace/arc furnace/submerged arc furnace/basic oxygen furnace/hot rolling reheated furnace'),
    ('O64 Stone crushers'),
    ('O65 Surgical and medical products including prophylactics and latex'),
    ('O66 Teflon based products'),
    ('O68 Tobacco products including cigarettes and tobacco/opium processes'),
    ('O69 Transformer repairing/manufacturing (dry process only)'),
    ('O70 Tyres and tubes vulcanization/hot retreating'),
    ('O71 Vegetable oil manufacturing including solvent extraction and refinery/hydrogenated oils'),
    ('O72 Wire drawing and wire netting'),
    ('O73 Dry cell battery (excluding manufacturing of electrodes) and assembling & charging of acid lead battery on micro scale'),
    ('O74 Pharmaceutical formulation and for R&D purpose (For sustained release/extended release of drugs only and not for commercial purpose)'),
    ('O75 Synthetic resins'),
    ('O76 Synthetic rubber excluding molding'),
    ('O77 Cashew nut processing'),
    ('O78 Coffee seed processing'),
    ('O79 Parboiled Rice Mills'),
    ('O80 Foam manufacturing'),
    ('O83 Producer gas plant using conventional up drift coal gasification (linked to rolling mills glass and ceramic industry refectories for dedicated fuel supply)'),
    ('O14 Thermometer manufacturing'),
    ('O36 Heat treatment using oil fired furnace (without cyaniding)'),
    ('O40 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely -B148Paint and ink sludge/residues'),
    ('O41 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely - Brass Dross, Copper Dross, Copper Oxide Mill Scale, Copper Reverts, Cake & Residues, Waste Copper and copper alloys in dispersible form, Slags from copper processing for further processing or refining, Insulated Copper Wire, Scrap/copper with PVC sheathing including ISRI-code material namely Druid, Jelly filled Copper cables, Zinc Dross-Hot dip Galvanizers SLAB, Zinc Dross-Bottom Dross, Zinc ash/Skimming arising from galvanizing and die casting operations, Zinc ash/Skimming/other zinc bearing wastes arising from smelting and refining, Zinc ash and residues including zinc alloy residues in dispersible form'),
    ('O61 Rolling mill (oil or coal fired) and cold rolling mill'),
    ('O67 Thermocol manufacturing (with boiler)'),
    ('O81 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely -Used Oil – As per specifications prescribed from time to time'),
    ('O82 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely -Waste Oil ---As per specifications prescribed from time to time'),
    ('Restaurant activity having overall waste water generated upto 10 KLD'),
    ('O84 Railway Stations (Waste Water Generation >= 10 KLD, but <100 KLD)'),
    ('O85 Scrapping Centers (for End of Life of Vehicles and other scraps such as plant and machineries, structural material, railway coaches and wagons etc.)'),
    ('a. Collection, De-Pollution, Dismantling Centers and Shredding Centers'),
    ('b. Collection, De-Pollution, Dismantling Centers and Shredding Centers'),
    ('c. Shredding Centers (can include white goods*/other scraps also)'),
    ('O86 Compressed/Refined Bio-Gas Production from Bio-degradables Wastes'),
    ('O87 Dairy farms'),
    ('O90A Ready mix cement concrete'),
    ('Assaying & hallmarking centers'),
    ('O89 CBG plants based on Municipal Solid Waste (MSW)'),
    ('O90 CBG plants based on process waste (industrial/process liquid effluent & solid waste like press mud, organic sludge, molasses, etc.)'),
    ('R61 Dead Animal incineration plant (Carcass disposal plant)'),
    ('R89 Sterilization process by using Gamma Rays using COBALT-60'),
    ('R61 Railway Stations (Waste Water Generation >= 100 KLD)'),
    ('Pyrolysis oil from plastic waste'),
    ('Municipal corporation STP'),
    ('Restaurant activity having overall waste water generated more than 10 KLD'),
    ('R3 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely - Spent cleared metal catalyst containing copper, Spent cleared metal catalyst containing zinc'),
    ('R10 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely - Spent catalyst containing nickel, cadmium, Zinc, copper, arsenic, vanadium and cobalt'),
    ('R15 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely - Dismantlers Recycling Plants -- Components of waste electrical and electronic assemblies comprising accumulators and other batteries included on list A, mercury-switches, activated glass cullets from cathode-ray tubes and other activated glass and PCB-capacitors, or any other component contaminated with Schedule 2 constituents (e.g. cadmium, mercury, lead, polychlorinated biphenyl) to an extent that they exhibit hazard characteristics indicated in part C of this Schedule'),
    ('R18 Pulp & Paper (waste paper based without bleaching process to manufacture Kraft paper)'),
    ('R32 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008'),
    ('R33 Industries engaged in recycling/reprocessing/recovery/reuse of Hazardous Waste under schedule iv of HW(M, H & TBM) rules, 2008 - Items namely - Integrated Recycling Plants -- Components of waste electrical and electronic assemblies comprising accumulators and other batteries included on list A, mercury-switches, activated glass cullets from cathode-ray tubes and other activated glass and PCB-capacitors, or any other component contaminated with Schedule 2 constituents (e.g. cadmium, mercury, lead, polychlorinated biphenyl) to an extent that they exhibit hazard characteristics indicated in part C of this Schedule'),
    ('R54 Pulp & Paper (waste paper based units with bleaching process to manufacture writing & printing paper)'),
    ('R59 Pulp & Paper (Large-Agro + wood), Small Pulp & Paper (agro based-wheat straw/rice husk)'),
    ('R1 Isolated storage of hazardous chemicals (as per schedule of manufacturing, storage of hazardous chemicals rules, 1989 as amended)'),
    ('R2 Automobile Manufacturing (integrated facilities)'),
    ('R4 Manufacturing of lubricating oils, grease and petroleum based products'),
    ('R5 DG Set of capacity > 5 MVA'),
    ('R6 Industrial carbon including electrodes and graphite blocks, activated carbon, carbon black'),
    ('R7 Lead acid battery manufacturing (excluding assembling and charging of lead-acid battery in micro scale)'),
    ('R8 Phosphate rock processing plant'),
    ('R9 Power generation plant [except Wind and Solar renewable power plants of all capacities and Mini Hydel power plant of capacity <25MW]'),
    ('R11 Processes involving chlorinated hydrocarbons'),
    ('R12 Sugar (excluding Khandsari)'),
    ('R13 Fibre glass production and processing (excluding moulding)'),
    ('R14 Fire crackers manufacturing and bulk storage facilities'),
    ('R16 Milk processes and dairy products (integrated project)'),
    ('R17 Phosphorous and its compounds'),
    ('R19 Coke making, liquefaction, coal tar distillation or fuel gas making'),
    ('R20 Manufacturing of explosives, detonators, fuses including management and handling activities'),
    ('R21 Manufacturing of paints varnishes, pigments and intermediate (excluding blending/mixing)'),
    ('R22 Organic Chemicals manufacturing'),
    ('R23 Airports and Commercial Air Strips'),
    ('R24 Asbestos and asbestos based industries'),
    ('R25 Basic chemicals and electro chemicals and its derivatives including manufacturing of acid'),
    ('R26 Cement'),
    ('R27 Chlorates, per-chlorates & peroxides'),
    ('R28 Chlorine, fluorine, bromine, iodine and their compounds'),
    ('R29 Dyes and Dye-Intermediates'),
    ('R31 Hotels having overall waste-water generation @ 100 KLD and more'),
    ('R34 Manufacturing of glue and gelatin'),
    ('R35 Mining and ore beneficiation'),
    ('R36 Nuclear power plant'),
    ('R37 Pesticides (technical) (excluding formulation)'),
    ('R38 Photographic film and its chemicals'),
    ('R39 Railway locomotive workshop/Integrated road transport workshop/Authorized service centers'),
    ('R40 Yarn/Textile processing involving any effluent/emission generating processes including bleaching, dyeing, printing and colouring'),
    ('R41 Chlor Alkali'),
    ('R42 Ship Breaking Industries'),
    ('R43 Oil and gas extraction including CBM (offshore & on-shore extraction through drilling wells)'),
    ('R44 Industry or process involving metal surface treatment or process such as pickling/electroplating/paint stripping/heat treatment using cyanide bath/phosphating or finishing and anodizing/enamellings/galvanizing'),
    ('R45 Tanneries'),
    ('R46 Ports and harbour, jetties and dredging operations'),
    ('R47 Synthetic fibers including rayon, tyre cord, polyester filament yarn'),
    ('R48 Thermal Power Plants'),
    ('R49 Slaughter house (as per notification S.O.270(E) dated 26.03.2001) and meat processing industries, bone mill, processing of animal horn, hoofs and other body parts'),
    ('R50 Aluminium Smelter'),
    ('R51 Copper Smelter'),
    ('R52 Fertilizer (basic) (excluding formulation)'),
    ('R53 Iron & Steel (involving processing from ore/integrated steel plants) and or Sponge Iron units'),
    ('R55 Zinc Smelter'),
    ('R56 Oil Refinery (mineral Oil or Petro Refineries)'),
    ('R57 Petrochemicals Manufacturing (including processing of Emulsions of oil and water)'),
    ('R58 Pharmaceuticals'),
    ('R60 Distillery (molasses/grain/yeast based)'),
    ('W38 CBG plants (irrespective of the type of feed) producing Fermented Organic Manure (FOM) & Liquid Fermented Organic Manure (LFOM) as by-products'),
    ('Other');

INSERT INTO industry_link (id, industry_category_id, industry_scale_id, industry_type_id)
VALUES
    (
        uuid_generate_v4(),
        (SELECT id FROM industry_category WHERE name = 'GREEN'),
        (SELECT id FROM industry_scale WHERE name = 'LSI'),
        (SELECT id FROM industry_type WHERE name = 'O90A Ready mix cement concrete')
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM industry_category WHERE name = 'ORANGE'),
        (SELECT id FROM industry_scale WHERE name = 'MSI'),
        (SELECT id FROM industry_type WHERE name = 'R55 Zinc Smelter')
    );

INSERT INTO company_profile (
    id, 
    contact_person_id, 
    name, 
    email_id, 
    fax, 
    phone_number, 
    website, 
    established_at,
    registration_number,
    address_id
)
VALUES 
    (
        uuid_generate_v4(), 
        (SELECT id FROM contact_person WHERE name = 'Ajay Ojha'), 
        'Techknowgreen Ltd.', 
        (SELECT id FROM email WHERE email_id = 'it@techknowgreen.com'), 
        '123-456-7890', 
        '1234567890', 
        'www.techknowgreen.com', 
        '2018-08-20',
        '444444444444',
        NULL
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM contact_person WHERE name = 'Ritesh Gujar'), 
        'Techknowblue Ltd.', 
        (SELECT id FROM email WHERE email_id = 'it@techknowblue.com'), 
        '098-765-4321', 
        '0987654321', 
        'www.techknowblue.com', 
        '2018-08-27',
        '444444444444',
        NULL
    );

INSERT INTO address (
    id,
    street, 
    line2, 
    line3, 
    city, 
    state, 
    district, 
    country, 
    pincode, 
    village, 
    taluka, 
    plot_number, 
    ro, 
    sro
)
VALUES
    (
        uuid_generate_v4(),
        '101, 102', 
        'Ekta Society', 
        'Hem Opal', 
        'Wakdewadi', 
        'Maharashtra', 
        'Pune', 
        'India', 
        '411005', 
        'Village A', 
        'Taluka A', 
        'Plot No. 26', 
        'RO1', 
        'SRO1'
    ),
    (
        uuid_generate_v4(),
        'New Lane', 
        'Orchid Society', 
        'Gandhi Road', 
        'New Mumbai', 
        'Maharashtra', 
        'Mumbai', 
        'India', 
        '400001', 
        'Village A', 
        'Taluka A', 
        'Plot No. 26', 
        'RO1', 
        'SRO1'
    ),
    (
        uuid_generate_v4(),
        '456 Elm St', 
        'Block B', 
        'Karol Bagh', 
        'New Delhi', 
        'Delhi', 
        'New Delhi', 
        'India', 
        '110005', 
        'Village B', 
        'Taluka B', 
        'Plot 2', 
        'RO2', 
        'SRO2'
    ),
    (
        uuid_generate_v4(),
        '789 Oak St', 
        'Suite 100', 
        'Connaught Place', 
        'New Delhi', 
        'Delhi', 
        'New Delhi', 
        'India', 
        '110002', 
        'Village C', 
        'Taluka C', 
        'Plot 3', 
        'RO3', 
        'SRO3'
    ),
    (
        uuid_generate_v4(),
        '123 Maple St', 
        'Apt 200', 
        'Saket', 
        'Chennai', 
        'Tamil Nadu', 
        'Chennai', 
        'India', 
        '600001', 
        'Village D', 
        'Taluka D', 
        'Plot 4', 
        'RO4', 
        'SRO4'
    );


INSERT INTO company_unit (
    id, 
    company_profile_id, 
    address_id, 
    industry_link_id,
    name,
    type,
    email_id,
    unit_fax,
    unit_phone_number,
    work_day,
    working_hour
)
VALUES 
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
        (SELECT id FROM address WHERE district = 'Pune'), 
        (SELECT id FROM industry_link WHERE industry_category_id = (SELECT id FROM industry_category WHERE name = 'GREEN') AND industry_scale_id = (SELECT id FROM industry_scale WHERE name = 'LSI') AND industry_type_id = (SELECT id FROM industry_type WHERE name = 'O90A Ready mix cement concrete')),
        'TSL Main Unit',
        'MAIN',
        (SELECT id FROM email WHERE email_id = 'mainunit@techknowgreen.com'),
        '123-456-7890',
        '123-456-7890',
        5,
        8
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
        (SELECT id FROM address WHERE district = 'Mumbai'), 
        (SELECT id FROM industry_link WHERE industry_category_id = (SELECT id FROM industry_category WHERE name = 'ORANGE') AND industry_scale_id = (SELECT id FROM industry_scale WHERE name = 'MSI') AND industry_type_id = (SELECT id FROM industry_type WHERE name = 'R55 Zinc Smelter')),
        'TSL Sub Unit 1',
        'SUB',
        (SELECT id FROM email WHERE email_id = 'subunit1@techknowgreen.com'),
        '098-765-4321',
        '098-765-4321',
        6,
        7
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
        (SELECT id FROM address WHERE street = '456 Elm St'), 
        (SELECT id FROM industry_link WHERE industry_category_id = (SELECT id FROM industry_category WHERE name = 'GREEN') AND industry_scale_id = (SELECT id FROM industry_scale WHERE name = 'LSI') AND industry_type_id = (SELECT id FROM industry_type WHERE name = 'O90A Ready mix cement concrete')),
        'TSL Sub Unit 2',
        'SUB',
        (SELECT id FROM email WHERE email_id = 'subunit2@techknowgreen.com'),
        '111-222-3333',
        '111-222-3333',
        5,
        8
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_profile WHERE name = 'Techknowblue Ltd.'), 
        (SELECT id FROM address WHERE street = '789 Oak St'), 
        (SELECT id FROM industry_link WHERE industry_category_id = (SELECT id FROM industry_category WHERE name = 'ORANGE') AND industry_scale_id = (SELECT id FROM industry_scale WHERE name = 'MSI') AND industry_type_id = (SELECT id FROM industry_type WHERE name = 'R55 Zinc Smelter')),
        'TBL Main Unit',
        'MAIN',
        (SELECT id FROM email WHERE email_id = 'mainunit@techknowblue.com'),
        '444-555-6666',
        '444-555-6666',
        6,
        7
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_profile WHERE name = 'Techknowblue Ltd.'), 
        (SELECT id FROM address WHERE street = '123 Maple St'), 
        (SELECT id FROM industry_link WHERE industry_category_id = (SELECT id FROM industry_category WHERE name = 'GREEN') AND industry_scale_id = (SELECT id FROM industry_scale WHERE name = 'LSI') AND industry_type_id = (SELECT id FROM industry_type WHERE name = 'O90A Ready mix cement concrete')),
        'TBL Sub Unit 1',
        'SUB',
        (SELECT id FROM email WHERE email_id = 'subunit1@techknowblue.com'),
        '777-888-9999',
        '777-888-9999',
        5,
        8
    );

INSERT INTO users (
    id, 
    email_id,
    phone,
    username, 
    password, 
    enabled, 
    designation, 
    company_unit_id, 
    failed_login_count, 
    last_login_date, 
    locked, 
    status
)
VALUES
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = 'tsl@tsl.com'),
        '1234567890',
        'tsl', 
        '$2a$12$vYPpThZEkVYeGZl8TpVTmORZLzIDnJwYYhgeF6oz/cbo3WoQbxgBa', 
        TRUE, 
        'TSL', 
        NULL, 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM email WHERE email_id = 'director1@tsl.com'),
        '1234567890',
        'director1', 
        '$2a$12$A0A1tWXqhTmzEjQ6J90Qr.aXoDo0.3wsO8zNdBA8lF5DTCozTbcJy', 
        TRUE, 
        'Director', 
        (SELECT id FROM company_unit WHERE name = 'TSL Main Unit' AND type = 'MAIN'), 
        0,
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = 'admin1@tsl.com'),
        '1234567890',
        'admin1', 
        '$2a$12$k/ubhOxFf8v5JwJAd63TieQOxmrlK0..437AhLOk.TV05PKb59i8G', 
        TRUE, 
        'Administrator', 
        (SELECT id FROM company_unit WHERE name = 'TSL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '1env1@tsl.com'),
        '1234567890',
        '1env1', 
        '$2a$12$Yw2kVbSGVSqDuWVuUDrqh.874D4T6cv/Mttn/r068Z60V5/3AO4mW', 
        TRUE, 
        'Environment Officer', 
        (SELECT id FROM company_unit WHERE name = 'TSL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '1man1@tsl.com'),
        '1234567890',
        '1man1', 
        '$2a$12$75tj6mIUUTcqX4IP470h6.HFX6P988JjpTB3/oe.rtkyOluMKB9Hq', 
        TRUE, 
        'Manager', 
        (SELECT id FROM company_unit WHERE name = 'TSL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '1thp1@tsl.com'),
        '1234567890',
        '1thp1', 
        '$2a$12$JJHlrgEFtMm3Jlqph8DbO.RlwB8LspM1Atb.w2KFcKPCkSfQxaoI2', 
        TRUE, 
        'Third Party', 
        (SELECT id FROM company_unit WHERE name = 'TSL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '2env1@tsl.com'),
        '1234567890',
        '2env1', 
        '$2a$12$tZ.NVZ5l3HddcTySkhSQJeMDwgeez.hLlUqyb3eKJ/xClsBsOwbiy', 
        TRUE, 
        'Environment Officer',
        (SELECT id FROM company_unit WHERE name = 'TSL Sub Unit 1' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '2man1@tsl.com'),
        '1234567890',
        '2man1', 
        '$2a$12$Z8wtL5RctoddEqa0plb3r.G0PY86Df8sZhoSWvtGL.l5OsYQpaQM.', 
        TRUE, 
        'Manager', 
        (SELECT id FROM company_unit WHERE name = 'TSL Sub Unit 1' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '2thp1@tsl.com'),
        '1234567890',
        '2thp1', 
        '$2a$12$gAnw2JqDsToQZCAHzKSxlOtSieL.kRltNs4OxTquV1141YIDAEHvO', 
        TRUE, 
        'Third Party', 
        (SELECT id FROM company_unit WHERE name = 'TSL Sub Unit 1' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '3env1@tsl.com'),
        '1234567890',
        '3env1', 
        '$2a$12$bQNcfAa/kiv07uvvxTC7suNrPXYZ/8cdx.iQO68Ts8UUN4KrInnkG', 
        TRUE, 
        'Environment Officer',
        (SELECT id FROM company_unit WHERE name = 'TSL Sub Unit 2' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '3man1@tsl.com'),
        '1234567890',
        '3man1', 
        '$2a$12$vfTsBKRVwcOEVWe9HrPLVODOqG/FhR/swT.W.DUOcVbI7IkN.J/22', 
        TRUE, 
        'Manager', 
        (SELECT id FROM company_unit WHERE name = 'TSL Sub Unit 2' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '3thp1@tsl.com'),
        '1234567890',
        '3thp1', 
        '$2a$12$YPQWMWlsfJnr1VWRgcRvDuQg5sS3xhZSnOwjYzKw3FVi.pY3SjjLa', 
        TRUE, 
        'Third Party', 
        (SELECT id FROM company_unit WHERE name = 'TSL Sub Unit 2' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = 'director2@tsl.com'),
        '1234567890',
        'director2', 
        '$2a$12$grTYnOfAKAKOa0oVSm23c.uDOEolPuQdJ5Qbwb5cmEG6mBnUxcZqa', 
        TRUE, 
        'Director',
        (SELECT id FROM company_unit WHERE name = 'TBL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = 'admin2@tsl.com'),
        '1234567890',
        'admin2', 
        '$2a$12$GKdMJnElenSD7T4rnF9UeOh3A.EtMrDYa03Jzzh8s9hEzVN5gWVTm', 
        TRUE, 
        'Administrator', 
        (SELECT id FROM company_unit WHERE name = 'TBL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '1env2@tsl.com'),
        '1234567890',
        '1env2', 
        '$2a$12$3nMhKsVaaFgIrfo0gf5.rOqKfeDyglVJC5CmqJizdkmd8OcDRKR.6', 
        TRUE, 
        'Environment Officer',
        (SELECT id FROM company_unit WHERE name = 'TBL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '1man2@tsl.com'),
        '1234567890',
        '1man2', 
        '$2a$12$sYhu8GCoEJVaR0vKObq51eKDFHBe5O9o9TfOTEoTycFEv1xeygUpy', 
        TRUE,
        'Manager', 
        (SELECT id FROM company_unit WHERE name = 'TBL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '1thp2@tsl.com'),
        '1234567890',
        '1thp2', 
        '$2a$12$yb14H1zivxFTNkRafjHjxeY0N6X8fecHoDaj2o/3x4hZaoZ0Mp6re', 
        TRUE, 
        'Third Party', 
        (SELECT id FROM company_unit WHERE name = 'TBL Main Unit' AND type = 'MAIN'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '2env2@tsl.com'), 
        '1234567890',
        '2env2', 
        '$2a$12$6xIrQNKaFWuaTI9TOljfI.6exRQtzAAHxevPUxzjxIHql2yWO.AES', 
        TRUE, 
        'Environment Officer',
        (SELECT id FROM company_unit WHERE name = 'TBL Sub Unit 1' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '2man2@tsl.com'),
        '1234567890',
        '2man2', 
        '$2a$12$c6tS9FWXC3CsWOT09p4TaeGQK1u3gi7sU3M5G8Hgw7kijFnfOcPv.', 
        TRUE, 
        'Manager', 
        (SELECT id FROM company_unit WHERE name = 'TBL Sub Unit 1' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    ),
    (
        uuid_generate_v4(),
        (SELECT id FROM email WHERE email_id = '2thp2@tsl.com'),
        '1234567890',
        '2thp2', 
        '$2a$12$pVanQqO4U2xl5MxORv4V2urgyflfnxRwXYmTpwQw/thA73UWaU0Sy', 
        TRUE, 
        'Third Party', 
        (SELECT id FROM company_unit WHERE name = 'TBL Sub Unit 1' AND type = 'SUB'), 
        0, 
        NOW(), 
        FALSE, 
        'ACTIVE'
    );


INSERT INTO user_role (id, user_id, role_id)
VALUES
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = 'tsl'), (SELECT id FROM role WHERE name = 'ROLE_TSL')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = 'director1'), (SELECT id FROM role WHERE name = 'ROLE_DIRECTOR')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = 'admin1'), (SELECT id FROM role WHERE name = 'ROLE_ADMIN')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '1env1'), (SELECT id FROM role WHERE name = 'ROLE_ENVIRONMENT_OFFICER')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '1man1'), (SELECT id FROM role WHERE name = 'ROLE_MANAGEMENT')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '1thp1'), (SELECT id FROM role WHERE name = 'ROLE_THIRD_PARTY')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '2env1'), (SELECT id FROM role WHERE name = 'ROLE_ENVIRONMENT_OFFICER')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '2man1'), (SELECT id FROM role WHERE name = 'ROLE_MANAGEMENT')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '2thp1'), (SELECT id FROM role WHERE name = 'ROLE_THIRD_PARTY')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '3env1'), (SELECT id FROM role WHERE name = 'ROLE_ENVIRONMENT_OFFICER')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '3man1'), (SELECT id FROM role WHERE name = 'ROLE_MANAGEMENT')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '3thp1'), (SELECT id FROM role WHERE name = 'ROLE_THIRD_PARTY')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = 'director2'), (SELECT id FROM role WHERE name = 'ROLE_DIRECTOR')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = 'admin2'), (SELECT id FROM role WHERE name = 'ROLE_ADMIN')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '1env2'), (SELECT id FROM role WHERE name = 'ROLE_ENVIRONMENT_OFFICER')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '1man2'), (SELECT id FROM role WHERE name = 'ROLE_MANAGEMENT')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '1thp2'), (SELECT id FROM role WHERE name = 'ROLE_THIRD_PARTY')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '2env2'), (SELECT id FROM role WHERE name = 'ROLE_ENVIRONMENT_OFFICER')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '2man2'), (SELECT id FROM role WHERE name = 'ROLE_MANAGEMENT')),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username = '2thp2'), (SELECT id FROM role WHERE name = 'ROLE_THIRD_PARTY'));

INSERT INTO employee (
    id, 
    company_id, 
    user_id, 
    contact_person_id, 
    name, 
    gender, 
    date_of_birth, 
    form_status
)
VALUES (
    uuid_generate_v4(),
    (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
    (SELECT id FROM users WHERE username = 'tsl'), 
    (SELECT id FROM contact_person WHERE name = 'Ajay Ojha'), 
    'Dhananjay Yelwande', 
    'Male', 
    '1990-01-01',
    'incomplete'
),
(
    uuid_generate_v4(),
    (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
    (SELECT id FROM users WHERE username = 'director1'), 
    (SELECT id FROM contact_person WHERE name = 'Ajay Ojha'), 
    'Omkar Patil', 
    'Male', 
    '1990-01-01',
    'incomplete'
),
(
    uuid_generate_v4(),
    (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
    (SELECT id FROM users WHERE username = 'admin1'), 
    (SELECT id FROM contact_person WHERE name = 'Ritesh Gujar'), 
    'Omkar Patil', 
    'Male', 
    '1990-01-01', 
    'incomplete'
),
(
    uuid_generate_v4(),
    (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
    (SELECT id FROM users WHERE username = '1env1'), 
    (SELECT id FROM contact_person WHERE name = 'Ritesh Gujar'), 
    'Kajal Dudhe', 
    'Female', 
    '1990-01-01', 
    'incomplete'
),
(
    uuid_generate_v4(),
    (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
    (SELECT id FROM users WHERE username = '1man1'), 
    (SELECT id FROM contact_person WHERE name = 'Ritesh Gujar'), 
    'Rishikesh Kharade', 
    'Male', 
    '1990-01-01', 
    'incomplete'
),
(
    uuid_generate_v4(),
    (SELECT id FROM company_profile WHERE name = 'Techknowgreen Ltd.'), 
    (SELECT id FROM users WHERE username = '1thp1'), 
    (SELECT id FROM contact_person WHERE name = 'Ritesh Gujar'), 
    'Harish Buddy', 
    'Male', 
    '1990-01-01', 
    'incomplete'
);

INSERT INTO unit (id, name, symbol, unit_type, conversion_factor)
VALUES 
    (uuid_generate_v4(), 'Kilogram', 'kg', 'weight', 1.00000),
    (uuid_generate_v4(), 'Meter', 'm', 'length', 1.00000),
    (uuid_generate_v4(), 'Litre', 'L', 'count', 1.00000),
    (uuid_generate_v4(), 'Tonne', 't', 'weight', 1000.00000),
    (uuid_generate_v4(), 'Piece', 'pc', 'count', 1.00000);

INSERT INTO resource (id, unit_id, name, quantity, resource_type)
VALUES 
    (uuid_generate_v4(), (SELECT id FROM unit WHERE name = 'Kilogram'), 'Steel', 1000.00, 'raw_material'),
    (uuid_generate_v4(), (SELECT id FROM unit WHERE name = 'Meter'), 'Copper Wire', 500.00, 'raw_material'),
    (uuid_generate_v4(), (SELECT id FROM unit WHERE name = 'Litre'), 'Diesel', 200.00, 'fuel'),
    (uuid_generate_v4(), (SELECT id FROM unit WHERE name = 'Tonne'), 'Cement', 10.00, 'product'),
    (uuid_generate_v4(), (SELECT id FROM unit WHERE name = 'Piece'), 'Machine Part', 50.00, 'product');

INSERT INTO resource_transaction(id, company_unit_id, resource_id, transaction_type, quantity, timestamp)
VALUES
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_unit WHERE name = 'TSL Main Unit'), 
        (SELECT id FROM resource WHERE name = 'Steel'), 
        'IN', 
        50, 
        NOW()
    ),
    (
        uuid_generate_v4(), 
        (SELECT id FROM company_unit WHERE name = 'TBL Main Unit'), 
        (SELECT id FROM resource WHERE name = 'Diesel'), 
        'OUT', 
        20, 
        NOW()
    );

INSERT INTO email_template (id, name, subject, body) VALUES
    (
        uuid_generate_v4(), 
        'Account Creation', 
        'Welcome to Our Platform, {firstName}!', 
        'Dear {firstName} {lastName},\n\n' ||
        'I hope this email finds you well.\n\n' ||
        'Thank you for creating an account with us. We are excited to have you on board! Below are the details of your account:\n' ||
        '- Username: {username}\n' ||
        '- Email: {email}\n' ||
        '- Registration Date: {registrationDate}\n\n' ||
        'If you have any questions or need assistance, please feel free to reach out to us.\n\n' ||
        'Best regards,\n' ||
        '{senderName},\n' ||
        '{senderPosition},\n' ||
        '{senderPhone}\n\n' ||
        '{companyName}\n' ||
        '{companyAddress}\n' ||
        'TEL: {companyPhone}'
    ),
    (
        uuid_generate_v4(), 
        'Password Reset', 
        'Reset Your Password', 
        'Dear {firstName},\n\n' ||
        'We received a request to reset your password. Click the link below to reset it:\n' ||
        '{resetLink}\n\n' ||
        'If you did not request this, please ignore this email.\n\n' ||
        'Best regards,\n' ||
        '{senderName},\n' ||
        '{senderPosition},\n' ||
        '{senderPhone}\n\n' ||
        '{companyName}\n' ||
        '{companyAddress}\n' ||
        'TEL: {companyPhone}'
    ),
    (
        uuid_generate_v4(), 
        'Email Verification', 
        'Verify Your Email Address', 
        'Dear {firstName},\n\n' ||
        'Thank you for signing up! Please verify your email address by clicking the link below:\n' ||
        '{verificationLink}\n\n' ||
        'Best regards,\n' ||
        '{senderName},\n' ||
        '{senderPosition},\n' ||
        '{senderPhone}\n\n' ||
        '{companyName}\n' ||
        '{companyAddress}\n' ||
        'TEL: {companyPhone}'
    ),
    (
        uuid_generate_v4(), 
        'Account Deactivation', 
        'Your Account has been Deactivated', 
        'Dear {firstName},\n\n' ||
        'We regret to inform you that your account has been deactivated due to {reason}.\n' ||
        'If you believe this is an error, please contact our support team.\n\n' ||
        'Best regards,\n' ||
        '{senderName},\n' ||
        '{senderPosition},\n' ||
        '{senderPhone}\n\n' ||
        '{companyName}\n' ||
        '{companyAddress}\n' ||
        'TEL: {companyPhone}'
    );


INSERT INTO notification_type (id, name, description,type, priority)
VALUES
    (uuid_generate_v4(), 'Client Registration Request', 'from {companyName} .','REQUEST', 1),
    (uuid_generate_v4(), 'Consent Reminder', 'Your consent is about to expire. Click to review.','REMINDER', 2),
    (uuid_generate_v4(), 'Officer Created', 'A new officer has been created in Unit: {unitName} by {companyName}.','BASIC', 3),
    (uuid_generate_v4(), 'New Unit Added', 'A new unit has been added by {companyName}','BASIC', 3),
    (uuid_generate_v4(), 'Congratulations!', 'Your account has been successfully created. Let''s get started!','TSL', 3);

INSERT INTO notification (id, user_id, from_user, message, notification_type, trigger_date)
VALUES
    (uuid_generate_v4(), (SELECT id FROM users WHERE username='tsl'), (SELECT id FROM users WHERE username='admin1'), NULL, (SELECT id FROM notification_type WHERE name='Client Registration Request'), CURRENT_DATE),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username='tsl'), (SELECT id FROM users WHERE username='admin1'), NULL, (SELECT id FROM notification_type WHERE name='Officer Created'), CURRENT_DATE),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username='admin1'), (SELECT id FROM users WHERE username='tsl'), NULL, (SELECT id FROM notification_type WHERE name='Congratulations!'), CURRENT_DATE),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username='1man1'), (SELECT id FROM users WHERE username='tsl'), NULL, (SELECT id FROM notification_type WHERE name='Consent Reminder'), CURRENT_DATE),
    (uuid_generate_v4(), (SELECT id FROM users WHERE username='1env1'), (SELECT id FROM users WHERE username='tsl'), NULL, (SELECT id FROM notification_type WHERE name='Congratulations!'), CURRENT_DATE);