drop view if exists public_urls;
drop view if exists recent_activity;
drop view if exists search_index;
drop view if exists skill_details;
drop view if exists cat_details;
drop view if exists writing_details;
drop view if exists project_details;
drop view if exists work_details;
drop view if exists profile;


create table profile_facts (
  id varchar primary key,
  key varchar not null unique,
  value varchar not null,
  value_type varchar not null,
  constraint profile_fact_id_not_empty check (length(id) > 0),
  constraint profile_fact_key_not_empty check (length(key) > 0),
  constraint profile_fact_value_type_valid check (value_type in ('text', 'url', 'list'))
);

create table companies (
  id varchar primary key,
  name varchar not null unique,
  url varchar not null unique,
  kind varchar not null,
  constraint company_id_not_empty check (length(id) > 0),
  constraint company_url_absolute check (regexp_matches(url, '^https?://')),
  constraint company_kind_valid check (kind in ('current', 'past'))
);

create table writing_sources (
  id varchar primary key,
  name varchar not null unique,
  url varchar not null unique,
  constraint writing_source_id_not_empty check (length(id) > 0),
  constraint writing_source_url_absolute check (regexp_matches(url, '^https?://'))
);

create table tags (
  id varchar primary key,
  name varchar not null,
  constraint tag_id_not_empty check (length(id) > 0)
);

create table work (
  id varchar primary key,
  company_id varchar not null references companies(id),
  role varchar not null,
  start_year integer not null,
  end_year integer,
  summary varchar not null,
  constraint work_id_not_empty check (length(id) > 0),
  constraint work_year_range check (start_year between 1990 and 2100 and (end_year is null or end_year between start_year and 2100))
);

create table projects (
  id varchar primary key,
  title varchar not null,
  url varchar not null,
  summary varchar not null,
  status varchar not null,
  visibility varchar not null,
  constraint project_id_not_empty check (length(id) > 0),
  constraint project_url_absolute check (regexp_matches(url, '^https?://')),
  constraint project_status_valid check (status in ('public', 'in progress', 'archived')),
  constraint project_visibility_valid check (visibility in ('public', 'private'))
);

create table writing (
  id varchar primary key,
  title varchar not null,
  url varchar not null,
  source_id varchar not null references writing_sources(id),
  published_date date,
  published_year integer not null,
  published_precision varchar not null,
  summary varchar not null,
  external boolean not null,
  constraint writing_id_not_empty check (length(id) > 0),
  constraint writing_url_valid check (regexp_matches(url, '^https?://') or starts_with(url, '/')),
  constraint writing_year_valid check (published_year between 1990 and 2100),
  constraint writing_precision_valid check (published_precision in ('day', 'year')),
  constraint writing_date_consistent check (
    (published_precision = 'day' and published_date is not null and year(published_date) = published_year)
    or (published_precision = 'year' and published_date is null)
  )
);

create table links (
  id varchar primary key,
  label varchar not null,
  url varchar not null,
  kind varchar not null,
  constraint link_id_not_empty check (length(id) > 0),
  constraint link_url_absolute check (regexp_matches(url, '^https?://')),
  constraint link_kind_valid check (kind in ('code', 'profile', 'contact'))
);

create table pages (
  id varchar primary key,
  title varchar not null,
  url varchar not null,
  markdown_url varchar not null,
  summary varchar not null,
  kind varchar not null,
  constraint page_id_not_empty check (length(id) > 0),
  constraint page_url_local check (starts_with(url, '/')),
  constraint page_markdown_url_local check (starts_with(markdown_url, '/md/')),
  constraint page_kind_valid check (kind in ('index', 'page', 'post', 'tool'))
);

create table cats (
  id varchar primary key,
  name varchar,
  caption varchar not null,
  alt varchar not null,
  image_key varchar not null,
  orientation varchar not null,
  constraint cat_id_not_empty check (length(id) > 0),
  constraint cat_image_key_valid check (regexp_matches(image_key, '^cat-bento-[0-9][0-9]$')),
  constraint cat_orientation_valid check (orientation in ('landscape', 'portrait', 'square'))
);

create table skills (
  id varchar primary key,
  name varchar not null,
  category varchar not null,
  weight integer not null,
  evidence varchar,
  constraint skill_id_not_empty check (length(id) > 0),
  constraint skill_category_valid check (category in ('systems', 'data', 'infra', 'ai', 'leadership', 'product')),
  constraint skill_weight_valid check (weight between 0 and 100)
);

create table entity_tags (
  entity_kind varchar not null,
  entity_id varchar not null,
  tag_id varchar not null references tags(id),
  primary key (entity_kind, entity_id, tag_id),
  constraint entity_tag_kind_valid check (entity_kind in ('work', 'project', 'writing', 'cat', 'skill')),
  constraint entity_tag_entity_id_not_empty check (length(entity_id) > 0)
);

create table skill_evidence (
  skill_id varchar not null references skills(id),
  entity_kind varchar not null,
  entity_id varchar not null,
  label varchar not null,
  primary key (skill_id, entity_kind, entity_id),
  constraint skill_evidence_kind_valid check (entity_kind in ('work', 'project', 'writing')),
  constraint skill_evidence_entity_id_not_empty check (length(entity_id) > 0)
);

create view profile as
select key, value, value_type
from profile_facts;

create view work_details as
select
  w.id,
  c.name as company,
  c.url,
  w.role,
  w.start_year,
  w.end_year,
  case
    when w.end_year is null then cast(w.start_year as varchar) || '-present'
    else cast(w.start_year as varchar) || '-' || cast(w.end_year as varchar)
  end as display_period,
  w.summary,
  coalesce(
    (select list(tag_id order by tag_id) from entity_tags where entity_kind = 'work' and entity_id = w.id),
    []::varchar[]
  ) as tags
from work w
join companies c on c.id = w.company_id;

create view project_details as
select
  p.*,
  coalesce(
    (select list(tag_id order by tag_id) from entity_tags where entity_kind = 'project' and entity_id = p.id),
    []::varchar[]
  ) as tags
from projects p;

create view writing_details as
select
  w.id,
  w.title,
  w.url,
  s.name as source,
  s.url as source_url,
  w.published_date,
  w.published_year,
  w.published_precision,
  case
    when w.published_precision = 'day' then strftime(w.published_date, '%Y-%m-%d')
    else cast(w.published_year as varchar)
  end as display_date,
  w.summary,
  w.external,
  coalesce(
    (select list(tag_id order by tag_id) from entity_tags where entity_kind = 'writing' and entity_id = w.id),
    []::varchar[]
  ) as tags
from writing w
join writing_sources s on s.id = w.source_id;

create view cat_details as
select
  c.*,
  coalesce(
    (select list(tag_id order by tag_id) from entity_tags where entity_kind = 'cat' and entity_id = c.id),
    []::varchar[]
  ) as tags
from cats c;

create view skill_details as
select
  s.*,
  coalesce(
    (select list(tag_id order by tag_id) from entity_tags where entity_kind = 'skill' and entity_id = s.id),
    []::varchar[]
  ) as tags,
  coalesce(
    (select list(label order by label) from skill_evidence where skill_id = s.id),
    []::varchar[]
  ) as evidence_labels
from skills s;

create view search_index as
select 'profile' as kind, id, key as title, null::varchar as url, value as body, []::varchar[] as tags from profile_facts
union all
select 'work', id, company || ' - ' || role, url, summary, tags from work_details
union all
select 'project', id, title, url, summary, tags from project_details
union all
select 'writing', id, title, url, summary, tags from writing_details
union all
select 'page', id, title, url, summary, []::varchar[] from pages
union all
select 'cat', id, caption, null::varchar, alt, tags from cat_details
union all
select 'skill', id, name, null::varchar, coalesce(evidence, ''), tags from skill_details;

create view recent_activity as
select 'writing' as kind, id, title, url, display_date as when_text, summary from writing_details
union all
select 'work' as kind, id, company || ' - ' || role, url, display_period as when_text, summary from work_details;

create view public_urls as
select 'work' as kind, id, company as label, url from work_details where url is not null
union all
select 'project', id, title, url from projects
union all
select 'writing', id, title, url from writing
union all
select 'link', id, label, url from links
union all
select 'page', id, title, url from pages;
