CREATE TABLE skills_edu.human_resource_need_details (
  id uuid PRIMARY KEY REFERENCES public.labour_demands(id) ON DELETE RESTRICT,
  company_master_id uuid NOT NULL,
  cluster_master_id uuid,
  corridor_master_id uuid,
  zee_master_id uuid,
  zfi_master_id uuid,
  requires_qualified_hr boolean NOT NULL,
  profile_description text,
  field_of_study varchar(255),
  minimum_experience_months integer CHECK (minimum_experience_months IS NULL OR minimum_experience_months >= 0),
  quantity_unit varchar(50) DEFAULT 'professionals',
  engagement_type varchar(50),
  training_available boolean NOT NULL DEFAULT false,
  internship_places integer CHECK (internship_places IS NULL OR internship_places >= 0),
  priority varchar(20) CHECK (priority IS NULL OR priority IN ('low','medium','high','critical')),
  source_version varchar(100),
  confidence_level varchar(20) CHECK (confidence_level IS NULL OR confidence_level IN ('low','medium','high','confirmed')),
  visibility_level varchar(20) NOT NULL DEFAULT 'private' CHECK (visibility_level IN ('private','validated_users','public_summary')),
  responsible_user_id bigint NOT NULL,
  approved_by bigint,
  approved_at timestamptz,
  created_by bigint NOT NULL,
  updated_by bigint,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  CHECK ((requires_qualified_hr = false AND profile_description IS NULL) OR (requires_qualified_hr = true AND profile_description IS NOT NULL))
);

CREATE TABLE skills_edu.human_resource_need_skills (
  need_id uuid NOT NULL REFERENCES skills_edu.human_resource_need_details(id) ON DELETE CASCADE,
  skill_id uuid NOT NULL REFERENCES public.skills(id) ON DELETE RESTRICT,
  requirement_type varchar(20) NOT NULL CHECK (requirement_type IN ('required','desired')),
  minimum_level varchar(30),
  weight numeric(5,2) CHECK (weight IS NULL OR weight BETWEEN 0 AND 100),
  is_approved boolean NOT NULL DEFAULT false,
  approved_by bigint,
  approved_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (need_id, skill_id, requirement_type)
);

CREATE TABLE skills_edu.human_resource_need_languages (
  need_id uuid NOT NULL REFERENCES skills_edu.human_resource_need_details(id) ON DELETE CASCADE,
  language_code varchar(10) NOT NULL,
  minimum_level varchar(30) NOT NULL,
  requirement_type varchar(20) NOT NULL CHECK (requirement_type IN ('required','desired')),
  PRIMARY KEY (need_id, language_code)
);

CREATE TABLE skills_edu.human_resource_need_certifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  need_id uuid NOT NULL REFERENCES skills_edu.human_resource_need_details(id) ON DELETE CASCADE,
  certification_name varchar(255) NOT NULL,
  issuer_name varchar(255),
  requirement_type varchar(20) NOT NULL CHECK (requirement_type IN ('required','desired')),
  must_be_valid_on date,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_hr_need_skills_skill ON skills_edu.human_resource_need_skills(skill_id,requirement_type,is_approved);
CREATE TRIGGER trg_hr_needs_updated_at BEFORE UPDATE ON skills_edu.human_resource_need_details FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (approved_by) REFERENCES public.users(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (responsible_user_id) REFERENCES public.users(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (company_master_id) REFERENCES public.organizations(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (cluster_master_id) REFERENCES public.economic_clusters(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (corridor_master_id) REFERENCES public.development_corridors(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (zee_master_id) REFERENCES public.investment_zones(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_details ADD FOREIGN KEY (zfi_master_id) REFERENCES public.investment_zones(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.human_resource_need_skills ADD FOREIGN KEY (approved_by) REFERENCES public.users(id) ON DELETE RESTRICT;