normalization: normalized_ddl_schema.sql

DROP TABLE IF EXISTS event_analytics;
DROP TABLE IF EXISTS conflict_risk;
DROP TABLE IF EXISTS predictions;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS subscription;
DROP TABLE IF EXISTS plan;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS event_details;
DROP TABLE IF EXISTS locations;
DROP TABLE IF EXISTS actors;
DROP TABLE IF EXISTS actor_types;
DROP TABLE IF EXISTS countries;
DROP TABLE IF EXISTS events;

CREATE TABLE countries (
    country_code VARCHAR(5) PRIMARY KEY,
    country_name VARCHAR(100)
);

CREATE TABLE actor_types (
    type_code VARCHAR(10) PRIMARY KEY,
    type_description VARCHAR(100)
);

CREATE TABLE events (
    global_event_id SERIAL PRIMARY KEY,
    sql_date DATE NOT NULL,
    month_year VARCHAR(6) NOT NULL,
    year INT NOT NULL,
    fraction_date DECIMAL(8, 4) NOT NULL,
    is_root_event BOOLEAN NOT NULL,
    event_code VARCHAR(10) NOT NULL,
    goldstein_scale DECIMAL(5, 2),
    num_mentions INT,
    num_sources INT,
    num_articles INT,
    avg_tone DECIMAL(5, 2)
);

CREATE TABLE actors (
    actor_id SERIAL PRIMARY KEY,
    actor_code VARCHAR(10) NOT NULL,
    actor_name VARCHAR(100) NOT NULL,
    country_code VARCHAR(5),
    type_code VARCHAR(10),
    FOREIGN KEY (country_code) REFERENCES countries(country_code),
    FOREIGN KEY (type_code) REFERENCES actor_types(type_code)
);

CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    country_code VARCHAR(5),
    adm1_code VARCHAR(10),
    latitude DECIMAL(8, 6),
    longitude DECIMAL(9, 6),
    feature_id VARCHAR(20),
    FOREIGN KEY (country_code) REFERENCES countries(country_code)
);

CREATE TABLE event_details (
    global_event_id INT,
    actor_id INT,
    location_id INT,
    PRIMARY KEY (global_event_id, actor_id, location_id),
    FOREIGN KEY (global_event_id) REFERENCES events (global_event_id) ON DELETE CASCADE,
    FOREIGN KEY (actor_id) REFERENCES actors (actor_id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE
);

CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    last_login_date DATE,
    register_date DATE NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles (role_id) ON DELETE CASCADE
);

CREATE TABLE plan (
    plan_id SERIAL PRIMARY KEY,
    plan_name VARCHAR(50) NOT NULL,
    max_events INT,
    max_notifications INT,
    max_predictions INT DEFAULT 10
);

CREATE TABLE subscription (
    subscription_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plan (plan_id) ON DELETE CASCADE
);

CREATE TABLE notifications (
    notification_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    event_id INT NOT NULL,
    notification_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events (global_event_id) ON DELETE CASCADE
);

CREATE TABLE predictions (
    prediction_id SERIAL PRIMARY KEY,
    event_id INT NOT NULL,
    actor_id INT NOT NULL,
    predicted_date DATE NOT NULL,
    prediction_type VARCHAR(50) NOT NULL,
    confidence_score DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (event_id) REFERENCES events (global_event_id) ON DELETE CASCADE,
    FOREIGN KEY (actor_id) REFERENCES actors (actor_id) ON DELETE CASCADE
);

CREATE TABLE conflict_risk (
    conflict_id SERIAL PRIMARY KEY,
    actor1_id INT NOT NULL,
    actor2_id INT NOT NULL,
    risk_score DECIMAL(5, 2),
    predicted_date DATE NOT NULL,
    description TEXT,
    FOREIGN KEY (actor1_id) REFERENCES actors (actor_id) ON DELETE CASCADE,
    FOREIGN KEY (actor2_id) REFERENCES actors (actor_id) ON DELETE CASCADE
);

CREATE TABLE event_analytics (
    analytics_id SERIAL PRIMARY KEY,
    actor_id INT NOT NULL,
    event_id INT NOT NULL,
    date DATE NOT NULL,
    conflict_coefficient DECIMAL(5, 2),
    mentions_count INT,
    avg_tone DECIMAL(5, 2),
    description TEXT,
    FOREIGN KEY (actor_id) REFERENCES actors (actor_id) ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events (global_event_id) ON DELETE CASCADE
);