-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS actions;
DROP TABLE IF EXISTS key_results;
DROP TABLE IF EXISTS objectives;
DROP TABLE IF EXISTS quarters;
DROP TABLE IF EXISTS categories;

-- Create categories table
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

-- Create quarters table
CREATE TABLE quarters (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  focus VARCHAR(255)
);

-- Create objectives table
CREATE TABLE objectives (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  category_id INTEGER REFERENCES categories(id),
  quarter_id INTEGER REFERENCES quarters(id),
  status VARCHAR(50) NOT NULL DEFAULT 'at-risk',
  completion INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create key_results table
CREATE TABLE key_results (
  id SERIAL PRIMARY KEY,
  objective_id INTEGER REFERENCES objectives(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completion INTEGER NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'at-risk',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create actions table
CREATE TABLE actions (
  id SERIAL PRIMARY KEY,
  key_result_id INTEGER REFERENCES key_results(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  team_member VARCHAR(100),
  status VARCHAR(50) DEFAULT 'No Status',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for faster queries
CREATE INDEX idx_objective_category ON objectives(category_id);
CREATE INDEX idx_objective_quarter ON objectives(quarter_id);
CREATE INDEX idx_keyresult_objective ON key_results(objective_id);
CREATE INDEX idx_action_keyresult ON actions(key_result_id);
