tables_and_views.sql

CREATE SEQUENCE seq_accounts
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


SET default_with_oids = false;

--
-- TOC entry 202 (class 1259 OID 45928)
-- Name: accounts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE accounts (
    id integer DEFAULT nextval('seq_accounts'::regclass) NOT NULL,
    inila character varying(40) DEFAULT NULL::character varying,
    externalid character varying(9) DEFAULT NULL::character varying,
    login character varying(50) DEFAULT NULL::character varying,
    password character varying(64) DEFAULT NULL::character varying,
    email character varying(255) DEFAULT NULL::character varying,
    lastname character varying(30) NOT NULL,
    firstname character varying(30) NOT NULL,
    secondname character varying(30) NOT NULL,
    userroleid integer NOT NULL,
    activationcode character varying(40) DEFAULT NULL::character varying,
    isenabled smallint DEFAULT (1)::smallint NOT NULL
);


--
-- TOC entry 203 (class 1259 OID 45956)
-- Name: auth_tokens; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE auth_tokens (
    token character(40) NOT NULL,
    accountid integer NOT NULL,
    description character varying(60) DEFAULT ''::character varying NOT NULL,
    created timestamp(0) without time zone DEFAULT now() NOT NULL,
    accessed timestamp(0) without time zone DEFAULT '2000-01-01 00:00:00'::timestamp without time zone NOT NULL,
    mask integer DEFAULT 0 NOT NULL
);


--
-- TOC entry 171 (class 1259 OID 35310)
-- Name: seq_compound_discip; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_compound_discip
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 207 (class 1259 OID 46034)
-- Name: compound_disciplines; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE compound_disciplines (
    id integer DEFAULT nextval('seq_compound_discip'::regclass) NOT NULL,
    name character varying(200) DEFAULT 'Курс по выбору'::character varying,
    gradeid integer NOT NULL,
    specializationid integer NOT NULL
);


--
-- TOC entry 172 (class 1259 OID 35313)
-- Name: seq_departments; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_departments
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 208 (class 1259 OID 46053)
-- Name: departments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE departments (
    id integer DEFAULT nextval('seq_departments'::regclass) NOT NULL,
    name character varying(200) DEFAULT NULL::character varying,
    facultyid integer NOT NULL,
    externalid character varying(9)
);


--
-- TOC entry 173 (class 1259 OID 35316)
-- Name: seq_disciplines; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_disciplines
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 214 (class 1259 OID 46221)
-- Name: disciplines; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE disciplines (
    id integer DEFAULT nextval('seq_disciplines'::regclass) NOT NULL,
    externalid character varying(9) DEFAULT NULL::character varying,
    gradeid integer,
    subjectid integer NOT NULL,
    authorid integer NOT NULL,
    examtype exam_credit_grading_credit NOT NULL,
    semesterid integer NOT NULL,
    practicecount integer DEFAULT 0 NOT NULL,
    lecturecount integer DEFAULT 0 NOT NULL,
    labcount integer DEFAULT 0 NOT NULL,
    facultyid integer NOT NULL,
    milestone integer DEFAULT 0 NOT NULL,
    milestonedate date,
    subtype scientific_disciplinary_coursework,
    compounddiscid integer,
    maxrate integer DEFAULT 0 NOT NULL,
    currate integer DEFAULT 0 NOT NULL,
    islocked integer DEFAULT 0 NOT NULL
);


--
-- TOC entry 215 (class 1259 OID 46275)
-- Name: seq_disciplines_exam_type; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_disciplines_exam_type
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 216 (class 1259 OID 46277)
-- Name: disciplines_exam_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE disciplines_exam_type (
    id integer DEFAULT nextval('seq_disciplines_exam_type'::regclass) NOT NULL,
    examtypename character varying(40) NOT NULL
);


--
-- TOC entry 174 (class 1259 OID 35319)
-- Name: seq_disciplines_groups; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_disciplines_groups
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 218 (class 1259 OID 46318)
-- Name: disciplines_groups; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE disciplines_groups (
    id integer DEFAULT nextval('seq_disciplines_groups'::regclass) NOT NULL,
    disciplineid integer NOT NULL,
    groupid integer NOT NULL
);


--
-- TOC entry 175 (class 1259 OID 35322)
-- Name: seq_disciplines_students; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_disciplines_students
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 221 (class 1259 OID 46366)
-- Name: disciplines_students; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE disciplines_students (
    id integer DEFAULT nextval('seq_disciplines_students'::regclass) NOT NULL,
    disciplineid integer NOT NULL,
    type attach_detach NOT NULL,
    recordbookid integer
);


--
-- TOC entry 224 (class 1259 OID 46429)
-- Name: seq_disciplines_study_plans; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_disciplines_study_plans
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 225 (class 1259 OID 46431)
-- Name: disciplines_study_plans; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE disciplines_study_plans (
    id integer DEFAULT nextval('seq_disciplines_study_plans'::regclass) NOT NULL,
    disciplineid integer NOT NULL,
    studyplanid integer NOT NULL
);


--
-- TOC entry 176 (class 1259 OID 35325)
-- Name: seq_disciplines_teachers; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_disciplines_teachers
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 226 (class 1259 OID 46450)
-- Name: disciplines_teachers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE disciplines_teachers (
    id integer DEFAULT nextval('seq_disciplines_teachers'::regclass) NOT NULL,
    disciplineid integer NOT NULL,
    teacherid integer NOT NULL
);


--
-- TOC entry 177 (class 1259 OID 35328)
-- Name: seq_exam_period_options; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_exam_period_options
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 229 (class 1259 OID 46520)
-- Name: exam_period_options; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE exam_period_options (
    id integer DEFAULT nextval('seq_exam_period_options'::regclass) NOT NULL,
    submoduleid integer NOT NULL,
    recordbookid integer,
    type absence_pass
);


--
-- TOC entry 178 (class 1259 OID 35331)
-- Name: seq_faculties; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_faculties
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 204 (class 1259 OID 45984)
-- Name: faculties; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE faculties (
    id integer DEFAULT nextval('seq_faculties'::regclass) NOT NULL,
    name character varying(100) NOT NULL,
    abbr character varying(20) NOT NULL,
    externalid character varying(9) DEFAULT NULL::character varying
);


--
-- TOC entry 179 (class 1259 OID 35334)
-- Name: seq_grades; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_grades
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 206 (class 1259 OID 46026)
-- Name: grades; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE grades (
    id integer DEFAULT nextval('seq_grades'::regclass) NOT NULL,
    num integer NOT NULL,
    degree bachelor_master_specialist NOT NULL
);


--
-- TOC entry 230 (class 1259 OID 46540)
-- Name: groups_years; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE groups_years (
    groupid integer NOT NULL,
    year integer NOT NULL,
    specializationid integer NOT NULL,
    name character varying(50) DEFAULT NULL::character varying
);


--
-- TOC entry 180 (class 1259 OID 35337)
-- Name: seq_job_positions; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_job_positions
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 212 (class 1259 OID 46182)
-- Name: job_positions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE job_positions (
    id integer DEFAULT nextval('seq_job_positions'::regclass) NOT NULL,
    name character varying(200) NOT NULL
);


--
-- TOC entry 181 (class 1259 OID 35340)
-- Name: seq_logs_binds_groups; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_logs_binds_groups
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 231 (class 1259 OID 46564)
-- Name: logs_binds_groups; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE logs_binds_groups (
    id integer DEFAULT nextval('seq_logs_binds_groups'::regclass) NOT NULL,
    disciplineid integer NOT NULL,
    teacherid integer NOT NULL,
    groupid integer NOT NULL,
    date timestamp(0) without time zone DEFAULT now() NOT NULL,
    type attach_detach NOT NULL
);


--
-- TOC entry 182 (class 1259 OID 35343)
-- Name: seq_logs_binds_students; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_logs_binds_students
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 232 (class 1259 OID 46589)
-- Name: logs_binds_students; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE logs_binds_students (
    id integer DEFAULT nextval('seq_logs_binds_students'::regclass) NOT NULL,
    disciplineid integer NOT NULL,
    teacherid integer,
    date timestamp(0) without time zone DEFAULT now() NOT NULL,
    type attach_detach NOT NULL,
    recordbookid integer
);


--
-- TOC entry 183 (class 1259 OID 35346)
-- Name: seq_logs_rating; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_logs_rating
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 233 (class 1259 OID 46614)
-- Name: logs_rating; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE logs_rating (
    id integer DEFAULT nextval('seq_logs_rating'::regclass) NOT NULL,
    submoduleid integer NOT NULL,
    teacherid integer NOT NULL,
    rate integer NOT NULL,
    date timestamp(0) without time zone DEFAULT now() NOT NULL,
    action add_change_delete NOT NULL,
    recordbookid integer
);


--
-- TOC entry 184 (class 1259 OID 35349)
-- Name: seq_logs_signin; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_logs_signin
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 234 (class 1259 OID 46639)
-- Name: logs_signin; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE logs_signin (
    id integer DEFAULT nextval('seq_logs_signin'::regclass) NOT NULL,
    accountid integer NOT NULL,
    date timestamp(0) without time zone DEFAULT now() NOT NULL
);


--
-- TOC entry 200 (class 1259 OID 35604)
-- Name: seq_modules; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_modules
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 227 (class 1259 OID 46489)
-- Name: modules; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE modules (
    id integer DEFAULT nextval('seq_modules'::regclass) NOT NULL,
    name character varying(200) NOT NULL,
    ordernum integer NOT NULL,
    disciplineid integer NOT NULL,
    type regular_exam_bonus_extra NOT NULL DEFAULT 'regular'::regular_exam_bonus_extra
);


--
-- TOC entry 236 (class 1259 OID 46680)
-- Name: seq_record_books_plans; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_record_books_plans
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 237 (class 1259 OID 46682)
-- Name: record_books_plans; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE record_books_plans (
    id integer DEFAULT nextval('seq_record_books_plans'::regclass) NOT NULL,
    recordbookid integer NOT NULL,
    studyplanid integer NOT NULL,
    bindingtime timestamp(0) without time zone DEFAULT now() NOT NULL
);


--
-- TOC entry 191 (class 1259 OID 35371)
-- Name: seq_students_groups; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_students_groups
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 242 (class 1259 OID 46760)
-- Name: students_groups; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE students_groups (
    id integer DEFAULT nextval('seq_students_groups'::regclass) NOT NULL,
    groupid integer NOT NULL,
    semesterid integer NOT NULL,
    date date,
    state common_outlet_expulsion_leave DEFAULT 'common'::common_outlet_expulsion_leave NOT NULL,
    recordbookid integer
);


--
-- TOC entry 252 (class 1259 OID 46937)
-- Name: private_view_disciplines_recordbooks_without_type; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW private_view_disciplines_recordbooks_without_type AS
 SELECT DISTINCT disciplines_students.recordbookid,
    disciplines_students.disciplineid,
    disciplines.semesterid
   FROM (disciplines_students
     JOIN disciplines ON ((disciplines_students.disciplineid = disciplines.id)))
UNION
 SELECT DISTINCT students_groups.recordbookid,
    disciplines_groups.disciplineid,
    disciplines.semesterid
   FROM ((disciplines_groups
     JOIN disciplines ON ((disciplines_groups.disciplineid = disciplines.id)))
     LEFT JOIN students_groups ON ((((students_groups.groupid = disciplines_groups.groupid) AND (students_groups.semesterid = disciplines.semesterid)) AND (students_groups.state <= 'outlet'::common_outlet_expulsion_leave))))
  GROUP BY students_groups.recordbookid, disciplines_groups.disciplineid, disciplines.semesterid
UNION
 SELECT DISTINCT record_books_plans.recordbookid,
    disciplines_study_plans.disciplineid,
    disciplines.semesterid
   FROM ((disciplines_study_plans
     LEFT JOIN record_books_plans ON ((disciplines_study_plans.studyplanid = record_books_plans.studyplanid)))
     JOIN disciplines ON ((disciplines_study_plans.disciplineid = disciplines.id)))
  GROUP BY record_books_plans.recordbookid, disciplines_study_plans.disciplineid, disciplines.semesterid;


--
-- TOC entry 235 (class 1259 OID 46653)
-- Name: rating_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE rating_table (
    teacherid integer NOT NULL,
    submoduleid integer NOT NULL,
    rate integer NOT NULL,
    date date NOT NULL,
    recordbookid integer DEFAULT 0 NOT NULL
);


--
-- TOC entry 219 (class 1259 OID 46354)
-- Name: seq_record_books; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_record_books
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 220 (class 1259 OID 46356)
-- Name: record_books; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE record_books (
    id integer DEFAULT nextval('seq_record_books'::regclass) NOT NULL,
    studentid integer NOT NULL,
    externalid character varying(50) DEFAULT NULL::character varying
);


--
-- TOC entry 185 (class 1259 OID 35352)
-- Name: seq_recovery_tokens; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_recovery_tokens
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 238 (class 1259 OID 46702)
-- Name: recovery_tokens; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE recovery_tokens (
    id integer DEFAULT nextval('seq_recovery_tokens'::regclass) NOT NULL,
    accountid integer NOT NULL,
    date timestamp(0) without time zone DEFAULT now() NOT NULL,
    token character varying(100) NOT NULL,
    isused smallint DEFAULT (0)::smallint NOT NULL
);


--
-- TOC entry 187 (class 1259 OID 35359)
-- Name: seq_requests; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_requests
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 239 (class 1259 OID 46718)
-- Name: requests; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE requests (
    id integer DEFAULT nextval('seq_requests'::regclass) NOT NULL,
    accountid integer,
    title character varying(50) DEFAULT NULL::character varying,
    description text,
    date timestamp(0) without time zone DEFAULT now() NOT NULL,
    status opened_processed_closed DEFAULT 'opened'::opened_processed_closed,
    hasimage integer DEFAULT 0 NOT NULL
);


--
-- TOC entry 240 (class 1259 OID 46737)
-- Name: schema_version; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE schema_version (
    installed_rank integer NOT NULL,
    version character varying(50) DEFAULT NULL::character varying,
    description character varying(200) NOT NULL,
    type character varying(20) NOT NULL,
    script character varying(1000) NOT NULL,
    checksum integer,
    installed_by character varying(100) NOT NULL,
    installed_on timestamp(0) without time zone DEFAULT now() NOT NULL,
    execution_time integer NOT NULL,
    success smallint NOT NULL
);


--
-- TOC entry 188 (class 1259 OID 35362)
-- Name: seq_semesters; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_semesters
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 211 (class 1259 OID 46127)
-- Name: semesters; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE semesters (
    id integer DEFAULT nextval('seq_semesters'::regclass) NOT NULL,
    year integer NOT NULL,
    num integer NOT NULL
);


--
-- TOC entry 186 (class 1259 OID 35357)
-- Name: seq_report_params; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_report_params
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 189 (class 1259 OID 35365)
-- Name: seq_specializations; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_specializations
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 190 (class 1259 OID 35368)
-- Name: seq_students; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_students
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 243 (class 1259 OID 46790)
-- Name: seq_study_form; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_study_form
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 192 (class 1259 OID 35374)
-- Name: seq_study_groups; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_study_groups
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 222 (class 1259 OID 46407)
-- Name: seq_study_plans; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_study_plans
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 193 (class 1259 OID 35377)
-- Name: seq_subjects; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_subjects
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 194 (class 1259 OID 35380)
-- Name: seq_subjects_faculties; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_subjects_faculties
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 195 (class 1259 OID 35384)
-- Name: seq_submodules; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_submodules
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 196 (class 1259 OID 35387)
-- Name: seq_teachers; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_teachers
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 197 (class 1259 OID 35390)
-- Name: seq_text_mark; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_text_mark
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 198 (class 1259 OID 35393)
-- Name: seq_user_roles; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE seq_user_roles
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 205 (class 1259 OID 45997)
-- Name: specializations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE specializations (
    id integer DEFAULT nextval('seq_specializations'::regclass) NOT NULL,
    name character varying(200) DEFAULT NULL::character varying,
    abbr character varying(20) DEFAULT NULL::character varying,
    code character varying(12) DEFAULT NULL::character varying,
    facultyid integer NOT NULL
);


--
-- TOC entry 241 (class 1259 OID 46748)
-- Name: students; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE students (
    id integer DEFAULT nextval('seq_students'::regclass) NOT NULL,
    accountid integer NOT NULL
);


--
-- TOC entry 244 (class 1259 OID 46792)
-- Name: study_form; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE study_form (
    id integer DEFAULT nextval('seq_study_form'::regclass) NOT NULL,
    formname character varying(40) NOT NULL
);


--
-- TOC entry 217 (class 1259 OID 46298)
-- Name: study_groups; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE study_groups (
    id integer DEFAULT nextval('seq_study_groups'::regclass) NOT NULL,
    gradeid integer NOT NULL,
    groupnum integer NOT NULL,
    facultyid integer NOT NULL,
    formid fulltime_parttime_evening DEFAULT fulltime NOT NULL
);


--
-- TOC entry 223 (class 1259 OID 46409)
-- Name: study_plans; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE study_plans (
    id integer DEFAULT nextval('seq_study_plans'::regclass) NOT NULL,
    externalid integer NOT NULL,
    year integer,
    facultyid integer
);


--
-- TOC entry 209 (class 1259 OID 46084)
-- Name: subjects; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE subjects (
    id integer DEFAULT nextval('seq_subjects'::regclass) NOT NULL,
    externalid character varying(9) DEFAULT NULL::character varying,
    typeid integer DEFAULT (-1),
    name character varying(200) NOT NULL,
    abbr character varying(20) DEFAULT NULL::character varying
);


--
-- TOC entry 245 (class 1259 OID 46800)
-- Name: subjects_faculties; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE subjects_faculties (
    id integer DEFAULT nextval('seq_subjects_faculties'::regclass) NOT NULL,
    subjectid integer NOT NULL,
    facultyid integer NOT NULL
);


--
-- TOC entry 228 (class 1259 OID 46503)
-- Name: submodules; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE submodules (
    id integer DEFAULT nextval('seq_submodules'::regclass) NOT NULL,
    moduleid integer NOT NULL,
    maxrate integer NOT NULL,
    ordernum integer NOT NULL,
    name character varying(200) NOT NULL,
    description character varying(200) DEFAULT NULL::character varying,
    isused smallint DEFAULT (0)::smallint NOT NULL,
    type current_landmark_control DEFAULT 'CurrentControl'::current_landmark_control NOT NULL
);


--
-- TOC entry 213 (class 1259 OID 46190)
-- Name: teachers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE teachers (
    id integer DEFAULT nextval('seq_teachers'::regclass) NOT NULL,
    jobpositionid integer NOT NULL,
    departmentid integer,
    accountid integer NOT NULL,
    status integer DEFAULT 1,
    facultyid integer NOT NULL
);


--
-- TOC entry 246 (class 1259 OID 46820)
-- Name: text_mark; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE text_mark (
    id integer DEFAULT nextval('seq_text_mark'::regclass) NOT NULL,
    name character varying(100) NOT NULL
);


--
-- TOC entry 201 (class 1259 OID 45922)
-- Name: user_roles; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE user_roles (
    id integer DEFAULT nextval('seq_user_roles'::regclass) NOT NULL,
    type student_teacher NOT NULL,
    rolename character varying(30) NOT NULL,
    mark integer NOT NULL
);


--
-- TOC entry 247 (class 1259 OID 46889)
-- Name: view_disciplines; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW view_disciplines AS
 SELECT disciplines.id AS disciplineid,
    disciplines.authorid,
    disciplines.examtype,
    disciplines.lecturecount,
    disciplines.practicecount,
    disciplines.labcount,
    disciplines.semesterid,
    disciplines.islocked,
    disciplines.milestone,
    disciplines.subtype,
    disciplines.compounddiscid,
    disciplines.maxrate,
    disciplines.currate,
    grades.id AS gradeid,
    grades.num AS gradenum,
    grades.degree,
    subjects.id AS subjectid,
    subjects.name AS subjectname,
    subjects.abbr AS subjectabbr,
    faculties.id AS facultyid,
    faculties.name AS facultyname,
    faculties.abbr AS facultyabbr,
    compound_disciplines.name AS compounddiscname
   FROM ((((disciplines
     JOIN subjects ON ((subjects.id = disciplines.subjectid)))
     JOIN faculties ON ((faculties.id = disciplines.facultyid)))
     LEFT JOIN grades ON ((grades.id = disciplines.gradeid)))
     LEFT JOIN compound_disciplines ON ((compound_disciplines.id = disciplines.compounddiscid)));


--
-- TOC entry 253 (class 1259 OID 46947)
-- Name: view_disciplines_recordbooks; Type: VIEW; Schema: public; Owner: -
--

CREATE OR REPLACE VIEW public.view_disciplines_recordbooks
    AS SELECT private_view_disciplines_recordbooks_without_type.recordbookid,
           private_view_disciplines_recordbooks_without_type.disciplineid,
           private_view_disciplines_recordbooks_without_type.semesterid,
           disciplines_students."type"
       FROM private_view_disciplines_recordbooks_without_type
           JOIN record_books ON private_view_disciplines_recordbooks_without_type.recordbookid = record_books.id
           LEFT JOIN disciplines_students ON private_view_disciplines_recordbooks_without_type.recordbookid = disciplines_students.recordbookid AND private_view_disciplines_recordbooks_without_type.disciplineid = disciplines_students.disciplineid;

--
-- TOC entry 249 (class 1259 OID 46899)
-- Name: view_groups; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW view_groups AS
 SELECT study_groups.id AS groupid,
    study_groups.groupnum,
    groups_years.name AS groupname,
    grades.id AS gradeid,
    grades.num AS gradenum,
    grades.degree,
    specializations.id AS specid,
    specializations.name AS specname,
    specializations.abbr AS specabbr,
    specializations.code AS speccode,
    faculties.id AS facultyid,
    faculties.name AS facultyname,
    faculties.abbr AS facultyabbr,
    groups_years.year
   FROM ((((groups_years
     JOIN study_groups ON ((groups_years.groupid = study_groups.id)))
     JOIN specializations ON ((groups_years.specializationid = specializations.id)))
     JOIN grades ON ((study_groups.gradeid = grades.id)))
     JOIN faculties ON ((faculties.id = specializations.facultyid)));


--
-- TOC entry 248 (class 1259 OID 46894)
-- Name: view_groups_reduced; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW view_groups_reduced AS
 SELECT study_groups.id AS groupid,
    study_groups.groupnum,
    groups_years.name AS groupname,
    grades.id AS gradeid,
    grades.num AS gradenum,
    grades.degree,
    specializations.id AS specid,
    specializations.name AS specname,
    specializations.abbr AS specabbr,
    specializations.code AS speccode,
    faculties.id AS facultyid,
    faculties.name AS facultyname,
    faculties.abbr AS facultyabbr
   FROM (((((groups_years
     LEFT JOIN groups_years t ON (((groups_years.groupid = t.groupid) AND (groups_years.year < t.year))))
     JOIN study_groups ON ((groups_years.groupid = study_groups.id)))
     JOIN specializations ON ((groups_years.specializationid = specializations.id)))
     JOIN grades ON ((study_groups.gradeid = grades.id)))
     JOIN faculties ON ((faculties.id = specializations.facultyid)))
  WHERE (t.groupid IS NULL);


--
-- TOC entry 254 (class 1259 OID 46958)
-- Name: view_roadmap; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW view_roadmap AS
 SELECT modules.disciplineid,
    modules.id AS moduleid,
    modules.name AS modulename,
    modules.ordernum AS moduleordernum,
    modules.type AS moduletype,
    submodules.id AS submoduleid,
    submodules.name AS submodulename,
    submodules.ordernum AS submoduleordernum,
    submodules.maxrate AS submodulerate,
    submodules.type AS submoduletype,
    submodules.isused AS submoduleisused
   FROM (modules
     LEFT JOIN submodules ON ((submodules.moduleid = modules.id)));


--
-- TOC entry 251 (class 1259 OID 46909)
-- Name: view_students; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW view_students AS
 SELECT students.id AS studentid,
    accounts.externalid,
    accounts.lastname,
    accounts.firstname,
    accounts.secondname,
    students.accountid,
    record_books.id AS recordbookid,
    record_books.externalid AS recordbooknumber,
    students_groups.semesterid,
    view_groups_reduced.groupid,
    view_groups_reduced.groupnum,
    view_groups_reduced.groupname,
    view_groups_reduced.gradeid,
    view_groups_reduced.gradenum,
    view_groups_reduced.degree,
    view_groups_reduced.specid,
    view_groups_reduced.specname,
    view_groups_reduced.specabbr,
    view_groups_reduced.speccode,
    view_groups_reduced.facultyid,
    view_groups_reduced.facultyname,
    view_groups_reduced.facultyabbr
   FROM ((((students
     LEFT JOIN accounts ON ((accounts.id = students.accountid)))
     LEFT JOIN record_books ON ((record_books.studentid = students.id)))
     LEFT JOIN students_groups ON ((students_groups.recordbookid = record_books.id)))
     LEFT JOIN view_groups_reduced ON ((view_groups_reduced.groupid = students_groups.groupid)));


--
-- TOC entry 250 (class 1259 OID 46904)
-- Name: view_teachers; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW view_teachers AS
 SELECT teachers.id AS teacherid,
    accounts.lastname,
    accounts.firstname,
    accounts.secondname,
    teachers.accountid,
    job_positions.id AS jobpositionid,
    job_positions.name AS jobpositionname,
    departments.id AS depid,
    departments.name AS depname,
    faculties.id AS facultyid,
    faculties.name AS facultyname,
    faculties.abbr AS facultyabbr
   FROM ((((teachers
     LEFT JOIN accounts ON ((teachers.accountid = accounts.id)))
     LEFT JOIN departments ON ((departments.id = teachers.departmentid)))
     JOIN faculties ON ((teachers.facultyid = faculties.id)))
     JOIN job_positions ON ((job_positions.id = teachers.jobpositionid)));


--
-- TOC entry 210 (class 1259 OID 46122)
-- Name: years; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE years (
    num integer NOT NULL
);


CREATE SEQUENCE seq_logs_discipline
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


CREATE TABLE logs_discipline_forms (
  id integer DEFAULT nextval('seq_logs_discipline'::regclass) NOT NULL,
  account_id integer NOT NULL,
  discipline_id integer NOT NULL,
  stage integer NOT NULL,
  was_locked smallint NOT NULL,
  download_timestamp TIMESTAMP DEFAULT NOW() NOT NULL
);

CREATE SEQUENCE seq_lessons
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE SEQUENCE seq_lesson_types
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE SEQUENCE seq_attendance
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

create table lessons (
  id integer default nextval('seq_lessons'::regclass) NOT NULL,
  discipline_id integer NOT NULL,
  lesson_date date NOT NULL,
  lesson_type integer NOT NULL,
  lesson_deleted smallint default 0 NOT NULL
);

create table lesson_types (
  id integer default nextval('seq_lesson_types'::regclass) NOT NULL,
  lesson_type character varying(20) NOT NULL
);

create table attendance (
  id integer default nextval('seq_attendance'::regclass) NOT NULL,
  lesson_id integer NOT NULL,
  recordbook_id integer NOT NULL,
  student_attended smallint default 0 NOT NULL,
  record_date timestamp default current_timestamp NOT NULL
);

INSERT INTO lesson_types(id, lesson_type) values
(1, 'лекция'),
(2, 'лабораторная'),
(3, 'семинар'),
(4, 'практикум');