-- TILSNA · Core Knowledge Database — schema (PostgreSQL)
-- 12 tables: master data + relationship layer + governance.
-- Portable to MySQL / MSSQL / Oracle with minor type tweaks.

DROP TABLE IF EXISTS entity_relationships, evidence_sources, funding_programs, funding_sources,
  innovations, products, research_projects, companies, expertise_tags, researchers,
  organizations, industry_categories CASCADE;

CREATE TABLE industry_categories (
  industry_category_id SERIAL PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  name_th VARCHAR(255), name_en VARCHAR(255) NOT NULL,
  description TEXT, is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE organizations (
  organization_id SERIAL PRIMARY KEY,
  name_th VARCHAR(255), name_en VARCHAR(255),
  organization_type VARCHAR(100), country VARCHAR(100),
  website_url TEXT, description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE researchers (
  researcher_id SERIAL PRIMARY KEY,
  full_name_th VARCHAR(255), full_name_en VARCHAR(255),
  academic_position VARCHAR(255),
  organization_id INT REFERENCES organizations(organization_id),
  orcid VARCHAR(100), profile_summary TEXT, research_interest TEXT,
  verification_status VARCHAR(50), last_verified_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE expertise_tags (
  expertise_tag_id SERIAL PRIMARY KEY,
  tag_name_th VARCHAR(255), tag_name_en VARCHAR(255),
  industry_category_id INT REFERENCES industry_categories(industry_category_id)
);
CREATE TABLE companies (
  company_id SERIAL PRIMARY KEY,
  company_name_th VARCHAR(255), company_name_en VARCHAR(255),
  registration_no VARCHAR(100), parent_company VARCHAR(255),
  country_origin VARCHAR(100), thailand_status VARCHAR(255),
  business_model VARCHAR(255), thailand_footprint TEXT,
  has_thai_legal_entity BOOLEAN, regulator_status VARCHAR(255),
  main_regulator VARCHAR(255), customer_group TEXT, main_market_channel TEXT,
  has_thailand_manufacturing BOOLEAN, has_boi BOOLEAN, website_url TEXT,
  latest_data_year INT, verification_status VARCHAR(50),
  evidence_strength VARCHAR(50), priority_score INT, note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE research_projects (
  research_project_id SERIAL PRIMARY KEY,
  title_th TEXT, title_en TEXT, abstract TEXT, research_type VARCHAR(100),
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  technology_readiness_level VARCHAR(50), start_year INT, end_year INT,
  lead_organization_id INT REFERENCES organizations(organization_id),
  status VARCHAR(100), source_id INT, last_verified_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name_th VARCHAR(255), product_name_en VARCHAR(255),
  product_type VARCHAR(100), description TEXT,
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  regulatory_status VARCHAR(255), fda_license_no VARCHAR(100),
  market_status VARCHAR(100), target_customer_group TEXT, website_url TEXT,
  last_verified_date DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE innovations (
  innovation_id SERIAL PRIMARY KEY,
  innovation_name_th VARCHAR(255), innovation_name_en VARCHAR(255),
  innovation_type VARCHAR(100), description TEXT,
  industry_category_id INT REFERENCES industry_categories(industry_category_id),
  technology_readiness_level VARCHAR(50),
  intellectual_property_status VARCHAR(100),
  commercialization_status VARCHAR(100), last_verified_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE funding_sources (
  funding_source_id SERIAL PRIMARY KEY,
  organization_id INT REFERENCES organizations(organization_id),
  funding_source_name VARCHAR(255), country VARCHAR(100),
  funding_type VARCHAR(100), website_url TEXT, description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE funding_programs (
  funding_program_id SERIAL PRIMARY KEY,
  funding_source_id INT REFERENCES funding_sources(funding_source_id),
  program_name VARCHAR(255),
  primary_category_id INT REFERENCES industry_categories(industry_category_id),
  segment TEXT, thailand_relevance TEXT, thailand_footprint TEXT,
  stage VARCHAR(255), instrument VARCHAR(255), typical_ticket_size TEXT,
  target_beneficiary TEXT, application_period TEXT, website_url TEXT,
  evidence_strength VARCHAR(50), priority_score INT,
  verification_status VARCHAR(50), note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE evidence_sources (
  evidence_source_id SERIAL PRIMARY KEY,
  entity_type VARCHAR(100) NOT NULL, entity_id INT NOT NULL,
  source_title TEXT, source_url TEXT, source_type VARCHAR(100),
  evidence_strength VARCHAR(50), retrieved_date DATE, note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE entity_relationships (
  relationship_id SERIAL PRIMARY KEY,
  source_entity_type VARCHAR(100) NOT NULL, source_entity_id INT NOT NULL,
  target_entity_type VARCHAR(100) NOT NULL, target_entity_id INT NOT NULL,
  relationship_type VARCHAR(100) NOT NULL, relationship_strength VARCHAR(50),
  evidence_source_id INT REFERENCES evidence_sources(evidence_source_id),
  note TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
