-- Create a table for patients
CREATE TABLE Patients (
  pid INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  Phno INTEGER NOT NULL,
  Email VARCHAR NOT NULL,
  AppointmentID VARCHAR NOT NULL,
  Prescription TEXT NOT NULL
);

-- Insert some values into the Patients table
INSERT INTO Patients VALUES (1, 'Patchari', 'F', 124, '023@gmail.com', 'A123', 'ABCD');
INSERT INTO Patients VALUES (2, 'Talluri', 'M', 1234, '123@gmail.com', 'B123', 'ABCDE');
INSERT INTO Patients VALUES (3, 'Tarak', 'M', 12345, '12345@gmail.com', 'B1234', 'ABCDEF');

-- Select patients with gender 'M'
--SELECT * FROM Patients WHERE gender = 'M';


-- Create a table for records with a foreign key constraint referencing the Patients table
CREATE TABLE Records (
  Rid INTEGER PRIMARY KEY,
  Pid INTEGER,
  CONSTRAINT Records_FK FOREIGN KEY (Pid) REFERENCES Patients(pid)
);

-- Insert some values into the Records table
INSERT INTO Records VALUES (1, 1);
INSERT INTO Records VALUES (2, 2);

-- Select records for a specific patient (e.g., pid=2)
--SELECT * FROM Records WHERE pid = 2;

-- Create table for diagnoses
CREATE TABLE diagnoses (
  Did INTEGER PRIMARY KEY,
  Medicine TEXT NOT NULL,
  Type TEXT NOT NULL,
  Pid INTEGER,
  CONSTRAINT diagnoses_Fk FOREIGN KEY (Pid) REFERENCES Patients (Pid)
);

-- Insert values into the diagnoses table
INSERT INTO diagnoses VALUES (1, 'dolo', 'generic', 1);
INSERT INTO diagnoses VALUES (2, 'synergic', 'fungal', 2);
INSERT INTO diagnoses VALUES (3, 'typhoid', 'chicken', 3);

-- Select from the diagnoses table where Did=2
--SELECT * FROM diagnoses WHERE Did = 2;
-- Update table diagnoses

UPDATE diagnoses
SET Type = 'antibiotic'
WHERE Did = 1;

--select * from diagnoses where Did=1;

-- Delete the Email column from the Patients table

ALTER TABLE Patients
DROP COLUMN Email;
--SELECT * FROM Patients WHERE gender = 'M';

--SELECT patients.pid, diagnoses.Did, patients.name, diagnoses.Medicine
--FROM patients
--INNER JOIN diagnoses 
--ON patients.Pid = diagnoses.Pid;

CREATE INDEX records.pid ON records (patients);