wehub-resource-sync
646 lines
15 KiB
PL/PgSQL
646 lines
15 KiB
PL/PgSQL
-- Downloaded from: https://github.com/ii-habibi/Dental-Clinic/blob/214da734250e375ec01f7aef4fca580af37db0bb/Database_Schema.sql
|
|
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
-- Dumped from database version 17.2
|
|
-- Dumped by pg_dump version 17.2
|
|
|
|
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET transaction_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET xmloption = content;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
--
|
|
-- Name: update_timestamp(); Type: FUNCTION; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE FUNCTION public.update_timestamp() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
|
|
ALTER FUNCTION public.update_timestamp() OWNER TO postgres;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- Name: admins; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.admins (
|
|
id integer NOT NULL,
|
|
username character varying(50) NOT NULL,
|
|
password character varying(255) NOT NULL,
|
|
is_super_admin boolean DEFAULT false,
|
|
name character varying(20) NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.admins OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: admins_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.admins_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.admins_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: admins_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.admins_id_seq OWNED BY public.admins.id;
|
|
|
|
|
|
--
|
|
-- Name: appointment; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.appointment (
|
|
appointment_id integer NOT NULL,
|
|
patient_id integer,
|
|
doctor_id integer,
|
|
appointment_date date,
|
|
appointment_time time without time zone,
|
|
treatment_type character varying(255),
|
|
status character varying(50),
|
|
visit_type character varying(50),
|
|
notes text,
|
|
payment_status character varying(50),
|
|
amount numeric(10,2),
|
|
payment_date timestamp without time zone,
|
|
payment_message text
|
|
);
|
|
|
|
|
|
ALTER TABLE public.appointment OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: appointment_appointment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.appointment_appointment_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.appointment_appointment_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: appointment_appointment_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.appointment_appointment_id_seq OWNED BY public.appointment.appointment_id;
|
|
|
|
|
|
--
|
|
-- Name: audit_logs; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.audit_logs (
|
|
id integer NOT NULL,
|
|
admin_id integer NOT NULL,
|
|
appointment_id integer,
|
|
action_type character varying(50),
|
|
old_value jsonb,
|
|
new_value jsonb,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
expense_id integer,
|
|
income_id integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.audit_logs OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: audit_logs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.audit_logs_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.audit_logs_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: audit_logs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.audit_logs_id_seq OWNED BY public.audit_logs.id;
|
|
|
|
|
|
--
|
|
-- Name: blog; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.blog (
|
|
blog_id integer NOT NULL,
|
|
title character varying(255) NOT NULL,
|
|
content text,
|
|
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
|
|
author character varying(255)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.blog OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: blog_blog_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.blog_blog_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.blog_blog_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: blog_blog_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.blog_blog_id_seq OWNED BY public.blog.blog_id;
|
|
|
|
|
|
--
|
|
-- Name: doctors; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.doctors (
|
|
id integer NOT NULL,
|
|
name character varying(100) NOT NULL,
|
|
qualification character varying(255) NOT NULL,
|
|
expertise text NOT NULL,
|
|
photo character varying(255),
|
|
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
ALTER TABLE public.doctors OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: doctors_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.doctors_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.doctors_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: doctors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.doctors_id_seq OWNED BY public.doctors.id;
|
|
|
|
|
|
--
|
|
-- Name: expenses; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.expenses (
|
|
id integer NOT NULL,
|
|
doctor_id integer,
|
|
amount numeric(10,2) NOT NULL,
|
|
type character varying(50) NOT NULL,
|
|
description text,
|
|
date timestamp without time zone DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
ALTER TABLE public.expenses OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: expenses_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.expenses_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.expenses_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: expenses_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.expenses_id_seq OWNED BY public.expenses.id;
|
|
|
|
|
|
--
|
|
-- Name: incomes; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.incomes (
|
|
id integer NOT NULL,
|
|
description character varying(255) NOT NULL,
|
|
amount numeric(10,2) NOT NULL,
|
|
date date NOT NULL,
|
|
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
ALTER TABLE public.incomes OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: incomes_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.incomes_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.incomes_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: incomes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.incomes_id_seq OWNED BY public.incomes.id;
|
|
|
|
|
|
--
|
|
-- Name: patients; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.patients (
|
|
patient_id integer NOT NULL,
|
|
name character varying(255),
|
|
email character varying(255),
|
|
phone character varying(20),
|
|
age integer,
|
|
gender character varying(10),
|
|
address character varying(200)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.patients OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: patients_patient_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.patients_patient_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.patients_patient_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: patients_patient_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.patients_patient_id_seq OWNED BY public.patients.patient_id;
|
|
|
|
|
|
--
|
|
-- Name: services; Type: TABLE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TABLE public.services (
|
|
service_id integer NOT NULL,
|
|
name character varying(100) NOT NULL,
|
|
description text,
|
|
price numeric(10,2) NOT NULL,
|
|
created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
ALTER TABLE public.services OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: services_service_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE SEQUENCE public.services_service_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER SEQUENCE public.services_service_id_seq OWNER TO postgres;
|
|
|
|
--
|
|
-- Name: services_service_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER SEQUENCE public.services_service_id_seq OWNED BY public.services.service_id;
|
|
|
|
|
|
--
|
|
-- Name: admins id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.admins ALTER COLUMN id SET DEFAULT nextval('public.admins_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: appointment appointment_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.appointment ALTER COLUMN appointment_id SET DEFAULT nextval('public.appointment_appointment_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: audit_logs id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.audit_logs ALTER COLUMN id SET DEFAULT nextval('public.audit_logs_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: blog blog_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.blog ALTER COLUMN blog_id SET DEFAULT nextval('public.blog_blog_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: doctors id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.doctors ALTER COLUMN id SET DEFAULT nextval('public.doctors_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: expenses id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses ALTER COLUMN id SET DEFAULT nextval('public.expenses_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: incomes id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.incomes ALTER COLUMN id SET DEFAULT nextval('public.incomes_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: patients patient_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.patients ALTER COLUMN patient_id SET DEFAULT nextval('public.patients_patient_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: services service_id; Type: DEFAULT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.services ALTER COLUMN service_id SET DEFAULT nextval('public.services_service_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: admins admins_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.admins
|
|
ADD CONSTRAINT admins_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: admins admins_username_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.admins
|
|
ADD CONSTRAINT admins_username_key UNIQUE (username);
|
|
|
|
|
|
--
|
|
-- Name: appointment appointment_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.appointment
|
|
ADD CONSTRAINT appointment_pkey PRIMARY KEY (appointment_id);
|
|
|
|
|
|
--
|
|
-- Name: audit_logs audit_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.audit_logs
|
|
ADD CONSTRAINT audit_logs_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: blog blog_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.blog
|
|
ADD CONSTRAINT blog_pkey PRIMARY KEY (blog_id);
|
|
|
|
|
|
--
|
|
-- Name: doctors doctors_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.doctors
|
|
ADD CONSTRAINT doctors_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: expenses expenses_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses
|
|
ADD CONSTRAINT expenses_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: incomes incomes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.incomes
|
|
ADD CONSTRAINT incomes_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: patients patients_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.patients
|
|
ADD CONSTRAINT patients_email_key UNIQUE (email);
|
|
|
|
|
|
--
|
|
-- Name: patients patients_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.patients
|
|
ADD CONSTRAINT patients_pkey PRIMARY KEY (patient_id);
|
|
|
|
|
|
--
|
|
-- Name: services services_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.services
|
|
ADD CONSTRAINT services_pkey PRIMARY KEY (service_id);
|
|
|
|
|
|
--
|
|
-- Name: idx_appointment_payment_date; Type: INDEX; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE INDEX idx_appointment_payment_date ON public.appointment USING btree (payment_date);
|
|
|
|
|
|
--
|
|
-- Name: idx_expenses_date; Type: INDEX; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE INDEX idx_expenses_date ON public.expenses USING btree (date);
|
|
|
|
|
|
--
|
|
-- Name: idx_expenses_type; Type: INDEX; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE INDEX idx_expenses_type ON public.expenses USING btree (type);
|
|
|
|
|
|
--
|
|
-- Name: idx_incomes_amount; Type: INDEX; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE INDEX idx_incomes_amount ON public.incomes USING btree (amount);
|
|
|
|
|
|
--
|
|
-- Name: idx_incomes_date; Type: INDEX; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE INDEX idx_incomes_date ON public.incomes USING btree (date);
|
|
|
|
|
|
--
|
|
-- Name: incomes set_timestamp_incomes; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER set_timestamp_incomes BEFORE UPDATE ON public.incomes FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();
|
|
|
|
|
|
--
|
|
-- Name: services set_timestamp_services; Type: TRIGGER; Schema: public; Owner: postgres
|
|
--
|
|
|
|
CREATE TRIGGER set_timestamp_services BEFORE UPDATE ON public.services FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();
|
|
|
|
|
|
--
|
|
-- Name: appointment appointment_patient_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.appointment
|
|
ADD CONSTRAINT appointment_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES public.patients(patient_id);
|
|
|
|
|
|
--
|
|
-- Name: audit_logs audit_logs_admin_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.audit_logs
|
|
ADD CONSTRAINT audit_logs_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES public.admins(id);
|
|
|
|
|
|
--
|
|
-- Name: audit_logs audit_logs_appointment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.audit_logs
|
|
ADD CONSTRAINT audit_logs_appointment_id_fkey FOREIGN KEY (appointment_id) REFERENCES public.appointment(appointment_id) ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- Name: audit_logs audit_logs_expense_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.audit_logs
|
|
ADD CONSTRAINT audit_logs_expense_id_fkey FOREIGN KEY (expense_id) REFERENCES public.expenses(id);
|
|
|
|
|
|
--
|
|
-- Name: audit_logs audit_logs_income_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.audit_logs
|
|
ADD CONSTRAINT audit_logs_income_id_fkey FOREIGN KEY (income_id) REFERENCES public.incomes(id);
|
|
|
|
|
|
--
|
|
-- Name: expenses expenses_doctor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
|
--
|
|
|
|
ALTER TABLE ONLY public.expenses
|
|
ADD CONSTRAINT expenses_doctor_id_fkey FOREIGN KEY (doctor_id) REFERENCES public.doctors(id) ON DELETE SET NULL;
|
|
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|