DeepResearch

-- ============================================
-- DEEPER RESEARCH SYSTEM - Database Schema
-- ============================================

-- ตารางนักวิจัย
CREATE TABLE researchers (
researcher_id SERIAL PRIMARY KEY,
full_name VARCHAR(255) NOT NULL,
email VARCHAR(320) NOT NULL UNIQUE,
affiliation VARCHAR(500),
expertise_area VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_researchers_email ON researchers(email);
CREATE INDEX idx_researchers_expertise ON researchers(expertise_area);

-- ตารางโครงการวิจัย
CREATE TABLE research_projects (
project_id SERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
description TEXT,
status VARCHAR(50) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','in_progress','review','completed','archived')),
priority VARCHAR(20) DEFAULT 'medium'
CHECK (priority IN ('low','medium','high','critical')),
lead_researcher_id INTEGER NOT NULL REFERENCES researchers(researcher_id),
started_at TIMESTAMP,
deadline_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_projects_status ON research_projects(status);
CREATE INDEX idx_projects_lead ON research_projects(lead_researcher_id);
CREATE INDEX idx_projects_deadline ON research_projects(deadline_at);

-- ตารางหัวข้อวิจัย (Topics / Research Questions)
CREATE TABLE research_topics (
topic_id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL REFERENCES research_projects(project_id) ON DELETE CASCADE,
parent_topic_id INTEGER REFERENCES research_topics(topic_id),
title VARCHAR(500) NOT NULL,
description TEXT,
depth_level INTEGER NOT NULL DEFAULT 0,
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_topics_project ON research_topics(project_id);
CREATE INDEX idx_topics_parent ON research_topics(parent_topic_id);

-- ตารางแหล่งข้อมูล (Sources / References)
CREATE TABLE sources (
source_id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL REFERENCES research_projects(project_id) ON DELETE CASCADE,
source_type VARCHAR(50) NOT NULL
CHECK (source_type IN ('academic_paper','book','website','dataset','interview','report','other')),
title VARCHAR(500) NOT NULL,
authors VARCHAR(1000),
url VARCHAR(2048),
publication_date DATE,
publisher VARCHAR(500),
doi VARCHAR(255),
raw_content TEXT,
credibility_score DECIMAL(3,2) CHECK (credibility_score BETWEEN 0 AND 5),
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_sources_project ON sources(project_id);
CREATE INDEX idx_sources_type ON sources(source_type);
CREATE INDEX idx_sources_doi ON sources(doi);

-- ตารางผู้เขียนแหล่งข้อมูล (กรณีหลายคน)
CREATE TABLE source_authors (
source_author_id SERIAL PRIMARY KEY,
source_id INTEGER NOT NULL REFERENCES sources(source_id) ON DELETE CASCADE,
author_name VARCHAR(255) NOT NULL,
author_order INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_source_authors_source ON source_authors(source_id);

-- ตารางการเชื่อมโยงแหล่งข้อมูลกับหัวข้อ
CREATE TABLE topic_sources (
topic_source_id SERIAL PRIMARY KEY,
topic_id INTEGER NOT NULL REFERENCES research_topics(topic_id) ON DELETE CASCADE,
source_id INTEGER NOT NULL REFERENCES sources(source_id) ON DELETE CASCADE,
relevance_score DECIMAL(3,2) CHECK (relevance_score BETWEEN 0 AND 5),
notes TEXT,
UNIQUE (topic_id, source_id)
);

CREATE INDEX idx_topic_sources_topic ON topic_sources(topic_id);
CREATE INDEX idx_topic_sources_source ON topic_sources(source_id);

-- ตารางผลการวิจัย (Findings)
CREATE TABLE findings (
finding_id SERIAL PRIMARY KEY,
topic_id INTEGER NOT NULL REFERENCES research_topics(topic_id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
finding_type VARCHAR(50) DEFAULT 'observation'
CHECK (finding_type IN ('observation','insight','conclusion','hypothesis','contradiction')),
confidence_level VARCHAR(20) DEFAULT 'medium'
CHECK (confidence_level IN ('low','medium','high')),
created_by INTEGER NOT NULL REFERENCES researchers(researcher_id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_findings_topic ON findings(topic_id);
CREATE INDEX idx_findings_type ON findings(finding_type);
CREATE INDEX idx_findings_creator ON findings(created_by);

-- ตารางการอ้างอิงแหล่งข้อมูลในผลวิจัย
CREATE TABLE finding_citations (
citation_id SERIAL PRIMARY KEY,
finding_id INTEGER NOT NULL REFERENCES findings(finding_id) ON DELETE CASCADE,
source_id INTEGER NOT NULL REFERENCES sources(source_id) ON DELETE CASCADE,
citation_context TEXT,
page_number INTEGER,
UNIQUE (finding_id, source_id)
);

CREATE INDEX idx_citations_finding ON finding_citations(finding_id);
CREATE INDEX idx_citations_source ON finding_citations(source_id);

-- ตารางสมาชิกโครงการวิจัย
CREATE TABLE project_members (
member_id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL REFERENCES research_projects(project_id) ON DELETE CASCADE,
researcher_id INTEGER NOT NULL REFERENCES researchers(researcher_id) ON DELETE CASCADE,
role VARCHAR(50) NOT NULL DEFAULT 'member'
CHECK (role IN ('lead','co_lead','member','reviewer','advisor')),
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE (project_id, researcher_id)
);

CREATE INDEX idx_project_members_project ON project_members(project_id);
CREATE INDEX idx_project_members_researcher ON project_members(researcher_id);

-- ตารางบันทึกการวิจัย (Research Logs)
CREATE TABLE research_logs (
log_id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL REFERENCES research_projects(project_id) ON DELETE CASCADE,
researcher_id INTEGER NOT NULL REFERENCES researchers(researcher_id),
topic_id INTEGER REFERENCES research_topics(topic_id) ON DELETE SET NULL,
action_type VARCHAR(50) NOT NULL
CHECK (action_type IN ('source_added','finding_created','topic_created','review_comment','status_change','note')),
description TEXT NOT NULL,
logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_logs_project ON research_logs(project_id);
CREATE INDEX idx_logs_researcher ON research_logs(researcher_id);
CREATE INDEX idx_logs_topic ON research_logs(topic_id);
CREATE INDEX idx_logs_logged_at ON research_logs(logged_at);