CREATE TABLE products (
   product_id INT PRIMARY KEY,
   product_name VARCHAR(100),
   therapeutic_area VARCHAR(100),
   launch_year INT,
   list_price DECIMAL(10,2)
);

INSERT INTO products VALUES
(101, 'CardioMax', 'Cardiovascular', 2021, 1200.00),
(102, 'CardioPlus', 'Cardiovascular', 2023, 1500.00),
(103, 'GlucoCare', 'Diabetes', 2020, 900.00),
(104, 'GlucoFast', 'Diabetes', 2024, 1100.00),
(105, 'OncoLife', 'Oncology', 2019, 5000.00),
(106, 'OncoAdvance', 'Oncology', 2022, 6500.00),
(107, 'NeuroZen', 'Neurology', 2021, 2200.00),
(108, 'NeuroPlus', 'Neurology', 2023, 2800.00),
(109, 'Respira', 'Respiratory', 2020, 750.00),
(110, 'RespiraX', 'Respiratory', 2024, 950.00);

CREATE TABLE physicians (
   physician_id INT PRIMARY KEY,
   physician_name VARCHAR(100),
   specialty VARCHAR(100),
   city VARCHAR(100),
   region VARCHAR(50)
);

INSERT INTO physicians VALUES
(201, 'Dr. Sharma', 'Cardiologist', 'Mumbai', 'West'),
(202, 'Dr. Patel', 'Cardiologist', 'Ahmedabad', 'West'),
(203, 'Dr. Rao', 'Endocrinologist', 'Bangalore', 'South'),
(204, 'Dr. Reddy', 'Endocrinologist', 'Hyderabad', 'South'),
(205, 'Dr. Singh', 'Oncologist', 'Delhi', 'North'),
(206, 'Dr. Kapoor', 'Oncologist', 'Chandigarh', 'North'),
(207, 'Dr. Sen', 'Neurologist', 'Kolkata', 'East'),
(208, 'Dr. Das', 'Neurologist', 'Bhubaneswar', 'East'),
(209, 'Dr. Iyer', 'Pulmonologist', 'Chennai', 'South'),
(210, 'Dr. Mehta', 'Pulmonologist', 'Pune', 'West');


CREATE TABLE sales_reps (
   rep_id INT PRIMARY KEY,
   rep_name VARCHAR(100),
   region VARCHAR(50),
   manager VARCHAR(100)
);

INSERT INTO sales_reps VALUES
(301, 'Amit Kumar', 'West', 'Raj Mehta'),
(302, 'Priya Shah', 'West', 'Raj Mehta'),
(303, 'Rahul Nair', 'South', 'Anita Rao'),
(304, 'Sneha Reddy', 'South', 'Anita Rao'),
(305, 'Vikram Singh', 'North', 'Arjun Kapoor'),
(306, 'Neha Gupta', 'North', 'Arjun Kapoor'),
(307, 'Arindam Bose', 'East', 'Suman Sen'),
(308, 'Pooja Das', 'East', 'Suman Sen'),
(309, 'Karan Iyer', 'South', 'Anita Rao'),
(310, 'Riya Jain', 'West', 'Raj Mehta');

CREATE TABLE prescriptions (
   prescription_id INT PRIMARY KEY,
   product_id INT,
   physician_id INT,
   rep_id INT,
   prescription_date DATE,
   units_prescribed INT,
   FOREIGN KEY (product_id) REFERENCES products(product_id),
   FOREIGN KEY (physician_id) REFERENCES physicians(physician_id),
   FOREIGN KEY (rep_id) REFERENCES sales_reps(rep_id)
);

INSERT INTO prescriptions VALUES
(1001, 101, 201, 301, '2026-01-05', 120),
(1002, 101, 202, 302, '2026-01-12', 90),
(1003, 102, 201, 301, '2026-02-03', 140),
(1004, 102, 202, 302, '2026-02-15', 160),
(1005, 103, 203, 303, '2026-01-20', 200),
(1006, 103, 204, 304, '2026-02-10', 180),
(1007, 104, 203, 303, '2026-03-05', 240),
(1008, 104, 204, 304, '2026-03-18', 210),
(1009, 105, 205, 305, '2026-01-25', 70),
(1010, 106, 206, 306, '2026-02-20', 85),
(1011, 105, 205, 305, '2026-03-10', 95),
(1012, 107, 207, 307, '2026-01-15', 130),
(1013, 108, 208, 308, '2026-02-08', 150),
(1014, 107, 207, 307, '2026-03-22', 110),
(1015, 109, 209, 309, '2026-01-18', 300),
(1016, 110, 210, 310, '2026-02-25', 280),
(1017, 109, 209, 309, '2026-03-12', 320),
(1018, 101, 201, 301, '2026-03-28', 100),
(1019, 103, 203, 303, '2026-03-25', 220),
(1020, 106, 205, 305, '2026-03-30', 75);

CREATE TABLE marketing_spend (
   campaign_id INT PRIMARY KEY,
   product_id INT,
   region VARCHAR(50),
   campaign_month DATE,
   marketing_channel VARCHAR(50),
   spend DECIMAL(12,2),
   FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO marketing_spend VALUES
(401, 101, 'West', '2026-01-01', 'Digital', 50000.00),
(402, 101, 'West', '2026-02-01', 'Digital', 60000.00),
(403, 102, 'West', '2026-02-01', 'Physician Events', 85000.00),
(404, 103, 'South', '2026-01-01', 'Digital', 45000.00),
(405, 103, 'South', '2026-02-01', 'Physician Events', 55000.00),
(406, 104, 'South', '2026-03-01', 'Digital', 90000.00),
(407, 105, 'North', '2026-01-01', 'Medical Conference', 120000.00),
(408, 106, 'North', '2026-02-01', 'Medical Conference', 150000.00),
(409, 107, 'East', '2026-01-01', 'Digital', 65000.00),
(410, 108, 'East', '2026-02-01', 'Physician Events', 75000.00),
(411, 109, 'South', '2026-01-01', 'Digital', 40000.00),
(412, 110, 'West', '2026-02-01', 'Digital', 70000.00),
(413, 101, 'West', '2026-03-01', 'Physician Events', 80000.00),
(414, 103, 'South', '2026-03-01', 'Digital', 60000.00),
(415, 105, 'North', '2026-03-01', 'Medical Conference', 180000.00);