CREATE DATABASE IF NOT EXISTS charity_compass;
USE charity_compass;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    mobile VARCHAR(30),
    sms_consent BOOLEAN NOT NULL DEFAULT FALSE,
    email_consent BOOLEAN NOT NULL DEFAULT FALSE,
    created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    causes TEXT NOT NULL,
    support_types TEXT NOT NULL,
    location VARCHAR(160),
    budget VARCHAR(80),
    availability VARCHAR(80),
    motivation TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS charities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL UNIQUE,
    description TEXT NOT NULL,
    website_url VARCHAR(255),
    service_area VARCHAR(160),
    cause_tags TEXT NOT NULL,
    support_types TEXT NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS recommendation_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    charity_id INT NOT NULL,
    rank_position INT NOT NULL,
    score INT NOT NULL,
    explanation TEXT,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (charity_id) REFERENCES charities(id) ON DELETE CASCADE
);

INSERT INTO charities
    (name, description, website_url, service_area, cause_tags, support_types)
VALUES
    ('Feeding America', 'A nationwide network working to end hunger through food banks, meal programs, and community partnerships.', 'https://www.feedingamerica.org', 'United States', 'hunger,disaster', 'money,time,goods'),
    ('Habitat for Humanity', 'Builds and improves homes in partnership with people and families who need safe and affordable housing.', 'https://www.habitat.org', 'United States', 'housing', 'money,time,skills,goods'),
    ('DonorsChoose', 'Connects donors with public school classroom projects requested by teachers.', 'https://www.donorschoose.org', 'United States', 'education', 'money'),
    ('The Trevor Project', 'Provides crisis support, advocacy, and mental health resources for LGBTQ young people.', 'https://www.thetrevorproject.org', 'United States', 'health,justice', 'money,time'),
    ('Earthjustice', 'Uses legal advocacy to protect the environment, public health, and climate stability.', 'https://earthjustice.org', 'United States', 'environment,justice', 'money,skills'),
    ('American Red Cross', 'Supports disaster relief, blood donation, emergency preparedness, and military families.', 'https://www.redcross.org', 'United States', 'disaster,health,veterans', 'money,time,goods')
ON DUPLICATE KEY UPDATE name = VALUES(name);