CREATE INDEX IF NOT EXISTS depends_dependee_index ON depends(dependee);
CREATE INDEX IF NOT EXISTS content_filename_index ON content(filename);

BEGIN;

/* Combinations of two packages and files with conflicting content. */
DROP VIEW IF EXISTS contentconflict;
CREATE VIEW contentconflict AS
	SELECT p1.id AS pid1,
		p2.id AS pid2,
		p1.name AS name1,
		p2.name AS name2,
		p1.version AS version1,
		p2.version AS version2,
		p1.architecture AS architecture1,
		p2.architecture AS architecture2,
		p1.source AS source1,
		p2.source AS source2,
		p1.hasscripts AS hasscripts1,
		p2.hasscripts AS hasscripts2,
		p1.multiarch AS multiarch1,
		p2.multiarch AS multiarch2,
		c1.filename,
		c1.id AS cid1,
		c2.id AS cid2,
		c1.hash AS hash1,
		c2.hash AS hash2
	FROM package AS p1, package AS p2, content AS c1, content AS c2
	WHERE p1.id = c1.pid
		AND p2.id = c2.pid
		AND c1.filename = c2.filename
		AND c1.hash != c2.hash;

/* Candidates for satisfying architecture-enforcing dependencies considering
 * provides. */
DROP VIEW IF EXISTS archdepcandidate;
CREATE VIEW archdepcandidate AS
	SELECT p1.id AS dependerid, p2.id AS dependeeid,
		p2.multiarch IS 'same' AS ismasame
	FROM package AS p1, depends AS d, package AS p2
	WHERE p1.id = d.pid
		AND d.dependee = p2.name
		AND p2.multiarch IS NOT 'foreign'
		AND (p2.multiarch IS NOT 'allowed' OR d.archqual IS NOT 'any')
		AND (p1.architecture = p2.architecture
			OR p1.architecture = 'all'
			OR p2.architecture = 'all'
			OR p2.architecture = d.archqual)
	UNION ALL
	SELECT p1.id AS dependerid, p2.id AS dependeeid,
		p2.multiarch IS 'same' AS ismasame
	FROM package AS p1, depends AS d, provides AS r, package AS p2
	WHERE p1.id = d.pid
		AND d.dependee = r.provided
		AND r.pid = p2.id
		AND p2.multiarch IS NOT 'foreign'
		AND (p2.multiarch IS NOT 'allowed' OR d.archqual IS NOT 'any')
		AND (p1.architecture = p2.architecture
			OR p1.architecture = 'all'
			OR p2.architecture = 'all'
			OR p2.architecture = d.archqual);

/* Describe properties of binary package names. */
DROP VIEW IF EXISTS packageapi;
CREATE VIEW packageapi AS
	SELECT name,
		count(*) AS instances,
		count(CASE architecture WHEN 'all' THEN 1 ELSE NULL END)
			AS hasindep,
		CASE count(CASE architecture WHEN 'all' THEN NULL ELSE 1 END)
			WHEN 0 THEN 0 ELSE 1 END
			AS hasarchdep,
		CASE min(source) WHEN max(source) THEN source ELSE NULL END
			AS source,
		max(hasscripts) AS hasscripts,
		CASE min(multiarch)
			WHEN max(multiarch) THEN multiarch
			ELSE NULL END
			AS multiarch
	FROM package GROUP BY name;

/* Architecture-dependent packages. */
DROP VIEW IF EXISTS archpackage;
CREATE VIEW archpackage AS
	SELECT
		p1.name,
		p1.hasscripts,
	       	p1.multiarch,
		exists(
			SELECT 1 FROM package AS p2, archdepcandidate AS c
			WHERE p2.name = p1.name AND c.dependerid = p2.id)
			AS hasarchdeps,
		exists(
			SELECT 1 FROM package AS p2, archdepcandidate AS c
			WHERE p2.name = p1.name AND c.dependerid = p2.id
				AND NOT c.ismasame) AS hasnoncoinstdeps
	FROM packageapi AS p1
	WHERE p1.instances >= 3 AND p1.hasindep = 0;

/* Architecture-independent packages. */
DROP VIEW IF EXISTS indeppackage;
CREATE VIEW indeppackage AS
	SELECT
		id,
		name,
		version,
		source,
		hasscripts,
		multiarch,
		exists(SELECT 1 FROM archdepcandidate WHERE dependerid = id)
			AS hasarchdeps
	FROM package
	WHERE architecture = 'all';

/* Packages violating M-A:same by shipping conflicting files. */
DROP VIEW IF EXISTS masame_conflict;
CREATE VIEW masame_conflict AS
	SELECT name1 AS name, version1 AS version, architecture1, architecture2, filename
	FROM contentconflict
	WHERE name1 = name2
		AND version1 = version2
		AND multiarch1 = 'same'
		AND multiarch2 = 'same';

/* Packages that could be marked M-A:foreign, because they
 *  * are Architecture: all
 *  * do not have maintainer scripts
 *  * do not have architecture enforcing dependencies
 */
DROP VIEW IF EXISTS maforeign_candidate;
CREATE VIEW maforeign_candidate AS
	SELECT p1.name FROM indeppackage AS p1
	WHERE p1.multiarch = 'no'
		AND p1.hasscripts = 0
		AND p1.hasarchdeps = 0;

/* Packages that could be converted from Arch:any to Arch:all M-A:foreign,
 * because they
 *  * do not have maintainer scripts
 *  * do not ship conflicting files
 *  * do not ship different file sets
 *  * do not have architecture enforcing dependencies
 */
DROP VIEW IF EXISTS archall_candidate;
CREATE VIEW archall_candidate AS
	SELECT p1.name FROM archpackage AS p1
	WHERE p1.hasscripts = 0
		AND NOT EXISTS (
			SELECT 1 FROM contentconflict
			WHERE name1 = p1.name AND name2 = p1.name)
		AND NOT EXISTS (
			SELECT 1
			FROM package AS p2, content AS c2, package AS p3
			WHERE p2.name = p1.name
				AND c2.pid = p2.id
				AND p3.name = p1.name
				AND NOT EXISTS (
					SELECT 1 FROM content AS c5
					WHERE c5.pid = p3.id
						AND c5.filename = c2.filename))
		AND p1.hasarchdeps = 0;

/* Packages that coud be marked Multi-Arch:same, because they
 *  * are architecture dependent
 *  * do not have a Multi-Arch marking
 *  * do not have maintainer scripts
 *  * do not have file conflicts
 */
DROP VIEW IF EXISTS masame_candidate;
CREATE VIEW masame_candidate AS
	SELECT p1.name FROM archpackage AS p1
	WHERE p1.multiarch = 'no'
		AND p1.hasscripts = 0
		AND NOT p1.hasnoncoinstdeps
		AND NOT EXISTS (
			SELECT 1 FROM contentconflict
			WHERE name1 = p1.name AND name2 = p1.name);

/* Package 'depender' has a dependency on 'dependee' that can be annotated with
 * :any, because
 *  * 'depender' is architecture independent
 *  * 'depender' does not have maintainer scripts
 *  * the dependency on 'dependee' is the only architecture enforcing dependency
 *  * 'dependee' is Multi-Arch: allowed
 */
DROP VIEW IF EXISTS colonany_candidate;
CREATE VIEW colonany_candidate AS
	SELECT depender, dependee
	FROM (
		SELECT
			p1.name AS depender,
			p2.name AS dependee,
			CASE max(p2.multiarch)
				WHEN min(p2.multiarch) THEN p2.multiarch
				ELSE NULL END
				AS multiarch
		FROM indeppackage AS p1, archdepcandidate AS c, package AS p2
		WHERE p1.hasscripts = 0
			AND c.dependerid = p1.id
			AND c.dependeeid = p2.id
		GROUP BY p1.id, p2.name)
	GROUP BY depender
	HAVING count(dependee) = 1
		AND min(CASE multiarch WHEN 'allowed' THEN 1 ELSE 0 END) = 1;

/* Packages that are wrongly marked Multi-Arch:foreign, because they
 *  * are architecture dependent
 *  * ship a shared library in a public path
 *  * and the library is not a plugin for a contained program
 */
DROP VIEW IF EXISTS maforeign_library;
CREATE VIEW maforeign_library AS
	SELECT DISTINCT p.name
	FROM package AS p
	WHERE architecture != 'all'
		AND multiarch = 'foreign'
		AND EXISTS (
			SELECT 1 FROM content
			WHERE pid = p.id
				AND filename REGEXP '^\./(usr/)?lib/([a-z0-9_]*-linux-gnu[a-z0-9_]*/)?lib.*\.so$')
		AND NOT EXISTS (
			SELECT 1 FROM content
			WHERE pid = p.id
				AND filename REGEXP '^\./(usr/)?s?bin/.*');

/* Packages that should employ the m-a:interpreter workaround, because they
 *  * are known to expose the architecture of their dependencies (e.g. rust libraries)
 *  * are currently architecture-independent and multiarch:foreign
 *  * have an architecture-dependent dependency
 */
DROP VIEW IF EXISTS maworkaround_packages;
CREATE VIEW maworkaround_packages AS
	SELECT name
	FROM indeppackage AS i
	WHERE name LIKE 'librust%-dev'
		AND multiarch = 'foreign'
		AND EXISTS (SELECT 1 FROM archdepcandidate WHERE dependerid = i.id);

COMMIT;