-- ========== DDL: CREATE ==========
CREATE TABLE Donor (
    donor_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(60) UNIQUE NOT NULL,
    city VARCHAR(30),
    donor_type VARCHAR(15) NOT NULL,
    CHECK (donor_type IN ('Individual', 'Corporate'))
);


CREATE TABLE Campaign (
        campaign_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(80) NOT NULL,
    goal_amount DECIMAL(12,2) NOT NULL
        CHECK (goal_amount > 0),
    start_date DATE NOT NULL,
    status VARCHAR(15) DEFAULT 'Active'
);


CREATE TABLE Donation (
    donation_id INTEGER PRIMARY KEY AUTOINCREMENT,
    donor_id INT NOT NULL,
    campaign_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL
            CHECK (amount > 0),
    donation_date DATE NOT NULL,
    payment_mode VARCHAR(15) DEFAULT 'UPI',

    FOREIGN KEY (donor_id)
        REFERENCES Donor(donor_id),

    FOREIGN KEY (campaign_id)
        REFERENCES Campaign(campaign_id)
);
CREATE TABLE Expense (
    expense_id INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id INT NOT NULL,
    description VARCHAR(100),
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
    FOREIGN KEY (campaign_id) REFERENCES Campaign(campaign_id)
);


-- ========== DDL: ALTER, DROP ==========
ALTER TABLE Donor ADD phone VARCHAR(15);

ALTER TABLE Donor MODIFY phone VARCHAR(20);

ALTER TABLE Donor DROP COLUMN phone;

CREATE TABLE Temp (id INT);

DROP TABLE Temp;


-- ========== DML: INSERT ==========

INSERT INTO Donor (name, email, city, donor_type) VALUES
('Rahul Sharma', 'rahul@mail.com', 'Mumbai', 'Individual'),
('Priya Patil', 'priya@mail.com', 'Pune', 'Individual'),
('Tata Trust', 'csr@tata.com', 'Mumbai', 'Corporate'),
('Amit Verma', 'amit@mail.com', 'Delhi', 'Individual'),
('Anita Desai', 'anita@mail.com', 'Thane', 'Individual');


INSERT INTO Campaign (title, goal_amount, start_date, status) VALUES
('Education for All', 500000, '2026-01-15', 'Active'),
('Clean Water Drive', 300000, '2026-02-01', 'Active'),
('Flood Relief', 800000, '2026-07-01', 'Active');

-- DML: INSERT INTO Donation

INSERT INTO Donation
    (donor_id, campaign_id, amount, donation_date, payment_mode)
VALUES
    (1, 1, 5000, '2026-01-25', 'UPI'),
    (2, 1, 3000, '2026-02-10', 'Card'),
    (3, 1, 200000, '2026-02-20', 'Cheque'),
    (3, 2, 150000, '2026-03-01', 'Cheque'),
    (4, 2, 7500, '2026-03-15', 'UPI'),
    (1, 3, 10000, '2026-07-20', 'Card'),
    (3, 3, 300000, '2026-07-10', 'Cheque'),
    (2, 3, 2500, '2026-08-03', 'Cash');
-- DML: INSERT INTO Expense

INSERT INTO Expense (campaign_id, description, amount)
VALUES
    (1, 'School kits', 120000),
    (2, 'Water purifiers', 180000),
    (3, 'Food ration packs', 250000);


-- ========== DML: UPDATE, DELETE ==========

UPDATE Donor
SET city = 'Navi Mumbai'
WHERE donor_id = 1;

UPDATE Donation
SET amount = amount + 500
WHERE payment_mode = 'Cash';
DELETE FROM Expense
WHERE expense_id = 3;


-- ========== SELECT QUERIES ==========

-- 1. Selection & Projection
SELECT name, city FROM Donor;


-- 2. WHERE with relational + logical operators
SELECT *
FROM Donation
WHERE amount > 5000 AND payment_mode = 'Card';


-- 3. Arithmetic operator
SELECT description, amount, amount * 1.18 AS amount_with_tax
FROM Expense;
-- 4. ORDER BY
SELECT *
FROM Donation
ORDER BY amount DESC;

-- 5. GROUP BY + aggregate functions

SELECT campaign_id,
       COUNT(*) AS total_donations,
       SUM(amount) AS total_raised,
       AVG(amount) AS avg_donation
FROM Donation
GROUP BY campaign_id;


-- 6. JOIN

SELECT d.name, c.title, n.amount
FROM Donation n
JOIN Donor d ON n.donor_id = d.donor_id
JOIN Campaign c ON n.campaign_id = c.campaign_id;
-- 7. Nested query: donors who gave more than the average donation

SELECT name
FROM Donor
WHERE donor_id IN (
    SELECT donor_id
    FROM Donation
    WHERE amount > (
        SELECT AVG(amount)
        FROM Donation
    )
);


-- 8. Nested query: donors who never donated

SELECT name
FROM Donor
WHERE donor_id NOT IN (
    SELECT donor_id
    FROM Donation
);


-- ========== VIEW ==========

CREATE VIEW Campaign_Summary AS
SELECT c.title,
       c.goal_amount,
       IFNULL(SUM(n.amount), 0) AS raised
FROM Campaign c
LEFT JOIN Donation n
    ON c.campaign_id = n.campaign_id
GROUP BY c.campaign_id, c.title, c.goal_amount;


SELECT * FROM Campaign_Summary;


-- ========== CONSTRAINT TESTS ==========
-- Each query should FAIL; run separately and screenshot the errors.

-- 1. CHECK constraint: negative donation amount
-- INSERT INTO Donation (donor_id, campaign_id, amount, donation_date)
-- VALUES (1, 1, -500, '2026-10-01');

-- 2. UNIQUE constraint: duplicate email
-- INSERT INTO Donor (name, email, donor_type)
-- VALUES ('X', 'rahul@mail.com', 'Individual');

-- 3. FOREIGN KEY constraint: nonexistent donor_id
-- INSERT INTO Donation (donor_id, campaign_id, amount, donation_date)
-- VALUES (99, 1, 500, '2026-10-01');

-- 4. NOT NULL constraint: missing donor name
-- INSERT INTO Donor (email, donor_type)
-- VALUES ('t@mail.com', 'Individual');