seed.sql

CREATE TABLE IF NOT EXISTS departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  budget DECIMAL(12,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  ssn VARCHAR(11),
  department_id INTEGER REFERENCES departments(id),
  role VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS projects (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  department_id INTEGER REFERENCES departments(id),
  status VARCHAR(20) DEFAULT 'active',
  start_date DATE,
  end_date DATE
);

-- Seed departments
INSERT INTO departments (name, budget) VALUES
  ('Engineering', 2500000.00),
  ('Marketing', 800000.00),
  ('Sales', 1200000.00),
  ('Human Resources', 600000.00),
  ('Finance', 900000.00);

-- Seed users
INSERT INTO users (name, email, ssn, department_id, role) VALUES
  ('Alice Chen', 'alice.chen@example.com', '123-45-6789', 1, 'Senior Engineer'),
  ('Bob Martinez', 'bob.martinez@example.com', '234-56-7890', 1, 'Staff Engineer'),
  ('Carol Williams', 'carol.williams@example.com', '345-67-8901', 1, 'Engineering Manager'),
  ('David Kim', 'david.kim@example.com', '456-78-9012', 2, 'Marketing Director'),
  ('Eva Johnson', 'eva.johnson@example.com', '567-89-0123', 2, 'Content Strategist'),
  ('Frank Brown', 'frank.brown@example.com', '678-90-1234', 2, 'SEO Specialist'),
  ('Grace Lee', 'grace.lee@example.com', '789-01-2345', 3, 'Sales Manager'),
  ('Henry Davis', 'henry.davis@example.com', '890-12-3456', 3, 'Account Executive'),
  ('Irene Wilson', 'irene.wilson@example.com', '901-23-4567', 3, 'Sales Engineer'),
  ('James Taylor', 'james.taylor@example.com', '012-34-5678', 4, 'HR Director'),
  ('Karen White', 'karen.white@example.com', '111-22-3333', 4, 'Recruiter'),
  ('Leo Garcia', 'leo.garcia@example.com', '222-33-4444', 5, 'CFO'),
  ('Maria Rodriguez', 'maria.rodriguez@example.com', '333-44-5555', 5, 'Financial Analyst'),
  ('Nathan Park', 'nathan.park@example.com', '444-55-6666', 1, 'DevOps Engineer'),
  ('Olivia Thompson', 'olivia.thompson@example.com', '555-66-7777', 1, 'Junior Engineer');

-- Seed projects
INSERT INTO projects (name, description, department_id, status, start_date, end_date) VALUES
  ('Platform Migration', 'Migrate legacy monolith to microservices architecture', 1, 'active', '2026-01-15', '2026-06-30'),
  ('API Gateway', 'Implement centralized API gateway with rate limiting', 1, 'active', '2026-02-01', '2026-04-30'),
  ('MCP Integration', 'Build MCP server for internal tool access', 1, 'planning', '2026-03-01', NULL),
  ('Brand Refresh', 'Complete brand identity redesign', 2, 'active', '2026-01-01', '2026-03-31'),
  ('Content Pipeline', 'Automate content creation and distribution workflow', 2, 'active', '2026-02-15', '2026-05-31'),
  ('Q2 Campaign', 'Spring product launch marketing campaign', 2, 'planning', '2026-04-01', '2026-06-30'),
  ('Enterprise Sales Portal', 'Self-service portal for enterprise customers', 3, 'active', '2025-11-01', '2026-04-30'),
  ('CRM Migration', 'Migrate from legacy CRM to new platform', 3, 'completed', '2025-09-01', '2026-01-31'),
  ('Benefits Overhaul', 'Redesign employee benefits package', 4, 'active', '2026-01-01', '2026-06-30'),
  ('Hiring Pipeline', 'Streamline technical hiring process', 4, 'active', '2026-02-01', '2026-05-31'),
  ('Budget Automation', 'Automate quarterly budget reporting', 5, 'active', '2026-01-15', '2026-04-30'),
  ('Compliance Audit', 'Annual SOC 2 Type II audit preparation', 5, 'planning', '2026-04-01', '2026-07-31');