CREATE TABLE posts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		timestamp TEXT NOT NULL,
		sequence INTEGER NOT NULL,
		user_did TEXT NOT NULL,
		user_handle TEXT,
		record_key TEXT NOT NULL,
		permalink TEXT,
		action TEXT NOT NULL,
		cid TEXT,
		text TEXT,
		created_at TEXT,
		langs TEXT,
		reply TEXT,
		embed TEXT,
		facets TEXT,
		tags TEXT,
		labels TEXT,
		has_media BOOLEAN DEFAULT FALSE,
		blob_cids TEXT,
		raw_json TEXT NOT NULL,
		ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		UNIQUE(user_did, record_key)
	);
CREATE TABLE sqlite_sequence(name,seq);
CREATE INDEX idx_posts_timestamp ON posts(timestamp);
CREATE INDEX idx_posts_user_did ON posts(user_did);
CREATE INDEX idx_posts_user_handle ON posts(user_handle);
CREATE INDEX idx_posts_record_key ON posts(record_key);
CREATE INDEX idx_posts_action ON posts(action);
CREATE TABLE users (
		did TEXT PRIMARY KEY,
		handle TEXT NOT NULL,
		display_name TEXT,
		resolved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	);
CREATE INDEX idx_users_handle ON users(handle);
CREATE TABLE media (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		url TEXT NOT NULL UNIQUE,
		url_hash TEXT NOT NULL,
		content_hash TEXT,
		file_path TEXT,
		file_size INTEGER,
		content_type TEXT,
		status TEXT DEFAULT 'pending',
		downloaded_at TIMESTAMP,
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		error TEXT
	);
CREATE INDEX idx_media_url_hash ON media(url_hash);
CREATE INDEX idx_media_content_hash ON media(content_hash);
CREATE INDEX idx_media_status ON media(status);
CREATE TABLE hashtags (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		tag TEXT NOT NULL UNIQUE,
		first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		use_count INTEGER DEFAULT 1
	);
CREATE INDEX idx_hashtags_tag ON hashtags(tag);
CREATE TABLE posts_hashtags (
		post_id INTEGER NOT NULL,
		hashtag_id INTEGER NOT NULL,
		PRIMARY KEY (post_id, hashtag_id),
		FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
		FOREIGN KEY (hashtag_id) REFERENCES hashtags(id) ON DELETE CASCADE
	);
CREATE INDEX idx_posts_hashtags_post_id ON posts_hashtags(post_id);
CREATE INDEX idx_posts_hashtags_hashtag_id ON posts_hashtags(hashtag_id);
CREATE TABLE urls (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		url TEXT NOT NULL UNIQUE,
		domain TEXT NOT NULL,
		first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		use_count INTEGER DEFAULT 1
	);
CREATE INDEX idx_urls_url ON urls(url);
CREATE INDEX idx_urls_domain ON urls(domain);
CREATE TABLE posts_urls (
		post_id INTEGER NOT NULL,
		url_id INTEGER NOT NULL,
		PRIMARY KEY (post_id, url_id),
		FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
		FOREIGN KEY (url_id) REFERENCES urls(id) ON DELETE CASCADE
	);
CREATE INDEX idx_posts_urls_post_id ON posts_urls(post_id);
CREATE INDEX idx_posts_urls_url_id ON posts_urls(url_id);