| 1 | DROP TABLE IF EXISTS event_analytics;
|
|---|
| 2 | DROP TABLE IF EXISTS conflict_risk;
|
|---|
| 3 | DROP TABLE IF EXISTS predictions;
|
|---|
| 4 | DROP TABLE IF EXISTS notifications;
|
|---|
| 5 | DROP TABLE IF EXISTS subscription;
|
|---|
| 6 | DROP TABLE IF EXISTS plan;
|
|---|
| 7 | DROP TABLE IF EXISTS users;
|
|---|
| 8 | DROP TABLE IF EXISTS roles;
|
|---|
| 9 | DROP TABLE IF EXISTS event_details;
|
|---|
| 10 | DROP TABLE IF EXISTS locations;
|
|---|
| 11 | DROP TABLE IF EXISTS actors;
|
|---|
| 12 | DROP TABLE IF EXISTS events;
|
|---|
| 13 |
|
|---|
| 14 | CREATE TABLE events (
|
|---|
| 15 | global_event_id SERIAL PRIMARY KEY,
|
|---|
| 16 | sql_date DATE NOT NULL,
|
|---|
| 17 | month_year VARCHAR(6) NOT NULL,
|
|---|
| 18 | year INT NOT NULL,
|
|---|
| 19 | fraction_date DECIMAL(8, 4) NOT NULL,
|
|---|
| 20 | is_root_event BOOLEAN NOT NULL,
|
|---|
| 21 | event_code VARCHAR(10) NOT NULL,
|
|---|
| 22 | goldstein_scale DECIMAL(5, 2),
|
|---|
| 23 | num_mentions INT,
|
|---|
| 24 | num_sources INT,
|
|---|
| 25 | num_articles INT,
|
|---|
| 26 | avg_tone DECIMAL(5, 2)
|
|---|
| 27 | );
|
|---|
| 28 |
|
|---|
| 29 | CREATE TABLE actors (
|
|---|
| 30 | actor_id SERIAL PRIMARY KEY,
|
|---|
| 31 | actor_code VARCHAR(10) NOT NULL,
|
|---|
| 32 | actor_name VARCHAR(100) NOT NULL,
|
|---|
| 33 | country_code VARCHAR(5),
|
|---|
| 34 | type_code VARCHAR(10)
|
|---|
| 35 | );
|
|---|
| 36 |
|
|---|
| 37 | CREATE TABLE locations (
|
|---|
| 38 | location_id SERIAL PRIMARY KEY,
|
|---|
| 39 | full_name VARCHAR(100) NOT NULL,
|
|---|
| 40 | country_code VARCHAR(5),
|
|---|
| 41 | adm1_code VARCHAR(10),
|
|---|
| 42 | latitude DECIMAL(8, 6),
|
|---|
| 43 | longitude DECIMAL(9, 6),
|
|---|
| 44 | feature_id VARCHAR(20)
|
|---|
| 45 | );
|
|---|
| 46 |
|
|---|
| 47 | CREATE TABLE event_details (
|
|---|
| 48 | global_event_id INT,
|
|---|
| 49 | actor_id INT,
|
|---|
| 50 | location_id INT,
|
|---|
| 51 | PRIMARY KEY (global_event_id, actor_id, location_id),
|
|---|
| 52 | FOREIGN KEY (global_event_id) REFERENCES events (global_event_id) ON DELETE CASCADE,
|
|---|
| 53 | FOREIGN KEY (actor_id) REFERENCES actors (actor_id) ON DELETE CASCADE,
|
|---|
| 54 | FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE
|
|---|
| 55 | );
|
|---|
| 56 |
|
|---|
| 57 | CREATE TABLE roles (
|
|---|
| 58 | role_id SERIAL PRIMARY KEY,
|
|---|
| 59 | role_name VARCHAR(50) NOT NULL
|
|---|
| 60 | );
|
|---|
| 61 |
|
|---|
| 62 | CREATE TABLE users (
|
|---|
| 63 | user_id SERIAL PRIMARY KEY,
|
|---|
| 64 | first_name VARCHAR(50) NOT NULL,
|
|---|
| 65 | last_name VARCHAR(50) NOT NULL,
|
|---|
| 66 | email VARCHAR(100) UNIQUE NOT NULL,
|
|---|
| 67 | password_hash VARCHAR(255) NOT NULL,
|
|---|
| 68 | role_id INT NOT NULL,
|
|---|
| 69 | last_login_date DATE,
|
|---|
| 70 | register_date DATE NOT NULL,
|
|---|
| 71 | FOREIGN KEY (role_id) REFERENCES roles (role_id) ON DELETE CASCADE
|
|---|
| 72 | );
|
|---|
| 73 |
|
|---|
| 74 | CREATE TABLE plan (
|
|---|
| 75 | plan_id SERIAL PRIMARY KEY,
|
|---|
| 76 | plan_name VARCHAR(50) NOT NULL,
|
|---|
| 77 | max_events INT,
|
|---|
| 78 | max_notifications INT,
|
|---|
| 79 | max_predictions INT DEFAULT 10
|
|---|
| 80 | );
|
|---|
| 81 |
|
|---|
| 82 | CREATE TABLE subscription (
|
|---|
| 83 | subscription_id SERIAL PRIMARY KEY,
|
|---|
| 84 | user_id INT NOT NULL,
|
|---|
| 85 | plan_id INT NOT NULL,
|
|---|
| 86 | start_date DATE NOT NULL,
|
|---|
| 87 | end_date DATE,
|
|---|
| 88 | status VARCHAR(20) NOT NULL,
|
|---|
| 89 | FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
|
|---|
| 90 | FOREIGN KEY (plan_id) REFERENCES plan (plan_id) ON DELETE CASCADE
|
|---|
| 91 | );
|
|---|
| 92 |
|
|---|
| 93 | CREATE TABLE notifications (
|
|---|
| 94 | notification_id SERIAL PRIMARY KEY,
|
|---|
| 95 | user_id INT NOT NULL,
|
|---|
| 96 | event_id INT NOT NULL,
|
|---|
| 97 | notification_date DATE NOT NULL,
|
|---|
| 98 | status VARCHAR(20) NOT NULL,
|
|---|
| 99 | FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
|
|---|
| 100 | FOREIGN KEY (event_id) REFERENCES events (global_event_id) ON DELETE CASCADE
|
|---|
| 101 | );
|
|---|
| 102 |
|
|---|
| 103 | CREATE TABLE predictions (
|
|---|
| 104 | prediction_id SERIAL PRIMARY KEY,
|
|---|
| 105 | event_id INT NOT NULL,
|
|---|
| 106 | actor_id INT NOT NULL,
|
|---|
| 107 | predicted_date DATE NOT NULL,
|
|---|
| 108 | prediction_type VARCHAR(50) NOT NULL,
|
|---|
| 109 | confidence_score DECIMAL(5,2) NOT NULL,
|
|---|
| 110 | FOREIGN KEY (event_id) REFERENCES events (global_event_id) ON DELETE CASCADE,
|
|---|
| 111 | FOREIGN KEY (actor_id) REFERENCES actors (actor_id) ON DELETE CASCADE
|
|---|
| 112 | );
|
|---|
| 113 |
|
|---|
| 114 | CREATE TABLE conflict_risk (
|
|---|
| 115 | conflict_id SERIAL PRIMARY KEY,
|
|---|
| 116 | actor1_id INT NOT NULL,
|
|---|
| 117 | actor2_id INT NOT NULL,
|
|---|
| 118 | risk_score DECIMAL(5, 2),
|
|---|
| 119 | predicted_date DATE NOT NULL,
|
|---|
| 120 | description TEXT,
|
|---|
| 121 | FOREIGN KEY (actor1_id) REFERENCES actors (actor_id) ON DELETE CASCADE,
|
|---|
| 122 | FOREIGN KEY (actor2_id) REFERENCES actors (actor_id) ON DELETE CASCADE
|
|---|
| 123 | );
|
|---|
| 124 |
|
|---|
| 125 | CREATE TABLE event_analytics (
|
|---|
| 126 | analytics_id SERIAL PRIMARY KEY,
|
|---|
| 127 | actor_id INT NOT NULL,
|
|---|
| 128 | event_id INT NOT NULL,
|
|---|
| 129 | date DATE NOT NULL,
|
|---|
| 130 | conflict_coefficient DECIMAL(5, 2),
|
|---|
| 131 | mentions_count INT,
|
|---|
| 132 | avg_tone DECIMAL(5, 2),
|
|---|
| 133 | description TEXT,
|
|---|
| 134 | FOREIGN KEY (actor_id) REFERENCES actors (actor_id) ON DELETE CASCADE,
|
|---|
| 135 | FOREIGN KEY (event_id) REFERENCES events (global_event_id) ON DELETE CASCADE
|
|---|
| 136 | );
|
|---|
