DROP TABLE courts;
CREATE TABLE courts (
precinct integer,
place integer,
extractID integer,
courtURL nvarchar(1024),
streamURL nvarchar(1024)
);
INSERT INTO courts VALUES(1,1,305,'https://www.jp.hctx.net/1-1/default.htm','https://video.ibm.com/embed/23872866');
INSERT INTO courts VALUES(1,2,310,'https://www.jp.hctx.net/1-2/default.htm','https://video.ibm.com/embed/23872902');
INSERT INTO courts VALUES(2,1,315,'https://www.jp.hctx.net/2-1/default.htm','https://video.ibm.com/embed/23872792');
INSERT INTO courts VALUES(2,2,320,'https://www.jp.hctx.net/2-2/default.htm','https://video.ibm.com/embed/23872906');
INSERT INTO courts VALUES(3,1,325,'https://www.jp.hctx.net/3-1/default.htm','https://video.ibm.com/embed/23872913');
INSERT INTO courts VALUES(3,2,330,'https://www.jp.hctx.net/3-2/default.htm','https://video.ibm.com/embed/23872915');
INSERT INTO courts VALUES(4,1,335,'https://www.jp.hctx.net/4-1/default.htm','https://video.ibm.com/embed/23872925');
INSERT INTO courts VALUES(4,2,340,'https://www.jp.hctx.net/4-2/default.htm','https://video.ibm.com/embed/23872927');
INSERT INTO courts VALUES(5,1,345,'https://www.jp.hctx.net/5-1/default.htm','https://video.ibm.com/embed/23872940');
INSERT INTO courts VALUES(5,2,350,'https://www.jp.hctx.net/5-2/default.htm','https://video.ibm.com/embed/23872948');
INSERT INTO courts VALUES(6,1,355,'https://www.jp.hctx.net/6-1/default.htm','https://video.ibm.com/embed/23873840');
INSERT INTO courts VALUES(6,2,360,'https://www.jp.hctx.net/6-2/default.htm','https://video.ibm.com/embed/23873836');
INSERT INTO courts VALUES(7,1,365,'https://www.jp.hctx.net/7-1/default.htm','https://video.ibm.com/embed/23873956');
INSERT INTO courts VALUES(7,2,370,'https://www.jp.hctx.net/7-2/default.htm','https://video.ibm.com/embed/23873966');
INSERT INTO courts VALUES(8,1,375,'https://www.jp.hctx.net/8-1/default.htm','https://video.ibm.com/embed/23872892');
INSERT INTO courts VALUES(8,2,380,'https://www.jp.hctx.net/8-2/default.htm','https://video.ibm.com/embed/23873982');
DROP TABLE docket;
CREATE TABLE docket (
precinct INTEGER,
place INTEGER,
docket_dateTime datetime,
URL nvarchar(1024) COLLATE Latin1_General_100_CS_AS_SC_UTF8,
docket_ID INTEGER IDENTITY(1, 1) PRIMARY KEY
);
DROP TABLE cases;
CREATE TABLE cases (
casenumber nvarchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
case_URL nvarchar(1024) COLLATE Latin1_General_100_CS_AS_SC_UTF8,
odyssey_ID nvarchar(1024) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
odyssey_URL nvarchar(1024) COLLATE Latin1_General_100_CS_AS_SC_UTF8,
filed_Date nvarchar(32),
case_status nvarchar(256) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
case_ID INTEGER IDENTITY(1, 1) PRIMARY KEY
);
DROP TABLE docketedCases;
CREATE TABLE docketedCases (
precinct INTEGER,
place INTEGER,
docket_dateTime datetime,
casenumber nvarchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
claim nvarchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
docketCaseID INTEGER IDENTITY(1, 1) PRIMARY KEY
);
DROP TABLE party;
CREATE TABLE party (
casenumber nvarchar(64)COLLATE Latin1_General_100_CI_AS_SC_UTF8,
party_role nvarchar(32) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
party_name nvarchar(1024) COLLATE Latin1_General_100_CS_AS_SC_UTF8,
party_address nvarchar(4000) COLLATE Latin1_General_100_CS_AS_SC_UTF8,
party_ID INTEGER IDENTITY(1, 1) PRIMARY KEY
);
DROP TABLE events;
CREATE TABLE events (
casenumber nvarchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8,
eventDescription nvarchar(4000) COLLATE Latin1_General_100_CS_AS_SC_UTF8,
dateAdded nvarchar(32),
eventID INTEGER IDENTITY(1, 1) PRIMARY KEY
);
CREATE UNIQUE INDEX docketTime on docket (precinct, place, docket_dateTime);
CREATE UNIQUE INDEX docketedCase on docketedCases (precinct, place, docket_dateTime, casenumber);
CREATE UNIQUE INDEX casenum on cases (casenumber);
CREATE UNIQUE INDEX partyIdx on party (casenumber, party_role, party_name);
CREATE UNIQUE INDEX eventIdx on events (casenumber, eventDescription, dateAdded);