
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;


CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100),
    budget NUMERIC(12, 2),
    created_at TIMESTAMP,
    manager_name VARCHAR(100),
    headcount INTEGER,
    is_active BOOLEAN,
    region_code VARCHAR(10),
    note TEXT
);


CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    hire_date DATE,
    salary NUMERIC(10, 2),
    job_title VARCHAR(100),
    department_id INTEGER REFERENCES departments(id),
    is_active BOOLEAN,
    notes TEXT
);

INSERT INTO departments (name, location, budget, created_at, manager_name, headcount, is_active, region_code, note)
SELECT
    'Department ' || i,
    'Location ' || (i % 5 + 1),
    round((100000 + random() * 900000)::numeric, 2),
    NOW() - (i || ' days')::interval,
    'Manager ' || i,
    (random() * 50)::int,
    (random() > 0.2),
    'R' || (i % 5 + 1),
    'Note for department ' || i
FROM generate_series(1, 2000) AS s(i);


INSERT INTO employees (
    first_name, last_name, email, phone, hire_date, salary, job_title,
    department_id, is_active, notes
)
SELECT
    'First' || i,
    'Last' || i,
    'user' || i || '@example.com',
    '555-01' || (1000 + i),
    CURRENT_DATE - (i % 365),
    round((random() * 90000 + 30000)::numeric, 2),
    'Job Title ' || (i % 10),
    (SELECT id FROM departments ORDER BY random() LIMIT 1),
    (random() > 0.1),
    'Note for employee ' || i
FROM generate_series(1, 2000) AS s(i);

