//users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT UNIQUE,
    password TEXT,
    status enum('active','inactive') NOT NULL,
    created_at DATE,
    updated_at DATE,
    created_by CHAR(50),
    updated_by CHAR(50)
);

//roles table
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at DATE,
    updated_at DATE,
    created_by CHAR(50),
    updated_by CHAR(50)
);

//user_role
CREATE TABLE user_role (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    role_id INTEGER,
    created_at DATE
);

INSERT INTO roles (name, created_at, updated_at, createdby)
VALUES
    ('Super Admin', '2023-08-03', '2023-08-03', '1'),
    ('Staff', '2023-08-03', '2023-08-03', '1');

CREATE TABLE user_account_types (
    id SERIAL PRIMARY KEY,
    user_id INT,
    account_type_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



// industries table
CREATE TABLE industries (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    status enum('active','inactive') NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

INSERT INTO industries (name,status,created_at, updated_at)
VALUES
    ('3D Printing / Additive Manufacturing','active','2023-08-25','2023-08-25'),
    ('Accommodation','active','2023-08-25','2023-08-25'),
    ('Agriculture','active','2023-08-25','2023-08-25'),
    ('ArgoTech (Agriculture Technology)','active','2023-08-25','2023-08-25'),
    ('AI (Artificial Intelligence)','active','2023-08-25','2023-08-25'),
    ('AR/VR (Augmented Reality/Virtual Reality)','active','2023-08-25','2023-08-25'),
    ('Arts','active','2023-08-25','2023-08-25'),
    ('Automotive','active','2023-08-25','2023-08-25'),
    ('Beauty and Cosmetics','active','2023-08-25','2023-08-25'),
    ('Biomedical Engineering','active','2023-08-25','2023-08-25'),
    ('Biotechnology (Biotech)','active','2023-08-25','2023-08-25'),
    ('Blockchain','active','2023-08-25','2023-08-25'),
    ('Business Intelligence (BI)','active','2023-08-25','2023-08-25'),
    ('Clean Energy (CleanTech)','active','2023-08-25','2023-08-25'),
    ('Construction','active','2023-08-25','2023-08-25'),
    ('Consulting','active','2023-08-25','2023-08-25'),
    ('Cryptocurrency (Crypto)','active','2023-08-25','2023-08-25'),
    ('Cybersecurity (Cybersec)','active','2023-08-25','2023-08-25'),
    ('Data Science','active','2023-08-25','2023-08-25'),
    ('E-commerce (E-comm)','active','2023-08-25','2023-08-25');

// specializations table
CREATE TABLE specializations (
    id SERIAL PRIMARY KEY,
    industry_id INT,
    name TEXT NOT NULL,
    status enum('active','inactive') NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

INSERT INTO specializations (name,industry_id,status,created_at, updated_at)
VALUES
    ('3D Printing / Additive Manufacturing',1,'active','2023-08-25','2023-08-25'),
    ('Accommodation',2,'active','2023-08-25','2023-08-25'),
    ('Agriculture',3,'active','2023-08-25','2023-08-25'),
    ('ArgoTech (Agriculture Technology)',4,'active','2023-08-25','2023-08-25'),
    ('AI (Artificial Intelligence)',5,'active','2023-08-25','2023-08-25'),
    ('AR/VR (Augmented Reality/Virtual Reality)',6,'active','2023-08-25','2023-08-25'),
    ('Arts',7,'active','2023-08-25','2023-08-25'),
    ('Automotive',8,'active','2023-08-25','2023-08-25'),
    ('Beauty and Cosmetics',9,'active','2023-08-25','2023-08-25'),
    ('Biomedical Engineering',10,'active','2023-08-25','2023-08-25'),
    ('Biotechnology (Biotech)',11,'active','2023-08-25','2023-08-25'),
    ('Blockchain',12,'active','2023-08-25','2023-08-25'),
    ('Business Intelligence (BI)',13,'active','2023-08-25','2023-08-25'),
    ('Clean Energy (CleanTech)',14,'active','2023-08-25','2023-08-25'),
    ('Construction',15,'active','2023-08-25','2023-08-25'),
    ('Consulting',16,'active','2023-08-25','2023-08-25'),
    ('Cryptocurrency (Crypto)',17,'active','2023-08-25','2023-08-25'),
    ('Cybersecurity (Cybersec)',18,'active','2023-08-25','2023-08-25'),
    ('Data Science',19,'active','2023-08-25','2023-08-25'),
    ('E-commerce (E-comm)',20,'active','2023-08-25','2023-08-25');


CREATE TABLE user_invites (
    id SERIAL PRIMARY KEY,
    invited_by INT,
    name TEXT
    email TEXT
    company_id INT,
    responsibility TEXT,
    status enum('active','inactive') NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE company_groups (
    id SERIAL PRIMARY KEY,
    name TEXT,
    group_order INT,
    is_default INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);


CREATE TABLE skills (
    id SERIAL PRIMARY KEY,
    category TEXT,
    name TEXT,
    status enum('active','inactive') NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP  
);

CREATE TABLE freelancer_skills (
    id SERIAL PRIMARY KEY,
    user_id INT,
    skill_id INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP  
);

CREATE TABLE project_marketing_channels (
    id SERIAL PRIMARY KEY,
    project_id INT,
    channel TEXT,
    channel_id INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP  
);

CREATE TABLE launch_leads (
    id SERIAL PRIMARY KEY,
    launch_id INT,
    project_id INT,
    marketing_channel_id TEXT,
    source TEXT,
    created_by INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP  
);

CREATE TABLE launch_lead_meta (
    id SERIAL PRIMARY KEY,
    lead_id INT,
    meta_key TEXT,
    meta_value TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP  
);