What is a Database?

-- This is your first database table!
-- It's like creating a new spreadsheet
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    favorite BOOLEAN DEFAULT FALSE
);

-- Add your first contact
INSERT INTO contacts (name, email, phone, favorite) 
VALUES ('Your Name', 'you@email.com', '123-456-7890', TRUE);

-- Create a more advanced contacts table
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    company TEXT,
    favorite BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Add multiple contacts at once
INSERT INTO contacts (name, email, phone, company, favorite) VALUES 
('Elon Musk', 'elon@tesla.com', '555-0001', 'Tesla', TRUE),
('Tim Cook', 'tim@apple.com', '555-0002', 'Apple', FALSE),
('Sundar Pichai', 'sundar@google.com', '555-0003', 'Google', FALSE),
('Your Best Friend', 'friend@email.com', '555-0004', NULL, TRUE);

-- Find all favorite contacts
SELECT * FROM contacts WHERE favorite = TRUE;

-- Find contacts from specific companies
SELECT name, email FROM contacts WHERE company = 'Apple';

-- Search by name (partial match)
SELECT * FROM contacts WHERE name LIKE '%Elon%';

-- Update a phone number
UPDATE contacts 
SET phone = '555-9999' 
WHERE name = 'Elon Musk';

-- Mark someone as favorite
UPDATE contacts 
SET favorite = TRUE 
WHERE email = 'tim@apple.com';

-- Add a category column to organize contacts
ALTER TABLE contacts ADD COLUMN category TEXT;

-- Update existing contacts with categories
UPDATE contacts SET category = 'Tech Leaders' 
WHERE company IN ('Tesla', 'Apple', 'Google');

UPDATE contacts SET category = 'Personal' 
WHERE company IS NULL;

-- Create a table to store notes about contacts
CREATE TABLE notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER,
    note TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

-- Add a note about a contact
INSERT INTO notes (contact_id, note) 
VALUES (1, 'Met at tech conference, interested in AI projects');

-- Count contacts by company
SELECT company, COUNT(*) as employee_count 
FROM contacts 
GROUP BY company;

-- Find recently added contacts
SELECT name, created_at 
FROM contacts 
ORDER BY created_at DESC 
LIMIT 5;

-- Add social media handles
ALTER TABLE contacts ADD COLUMN twitter TEXT;
ALTER TABLE contacts ADD COLUMN linkedin TEXT;

-- Find all contacts with social media
SELECT name, twitter, linkedin 
FROM contacts 
WHERE twitter IS NOT NULL OR linkedin IS NOT NULL;

-- Track meetings with contacts
CREATE TABLE meetings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER,
    meeting_date DATE,
    meeting_notes TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

-- Export contacts to share with team
SELECT name || ',' || email || ',' || phone as contact_info
FROM contacts
WHERE category = 'Tech Leaders';

-- Create a user access log
CREATE TABLE access_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT,
    user_id TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Log when someone views sensitive data
INSERT INTO access_log (action, user_id) 
VALUES ('viewed_contacts', 'admin_user');

-- Create a simple permission system
CREATE TABLE user_permissions (
    user_id TEXT PRIMARY KEY,
    can_read BOOLEAN DEFAULT TRUE,
    can_write BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE
);